HLOOKUP on multiple worksheets

  • Thread starter Thread starter cbrown
  • Start date Start date
C

cbrown

I have 1 report file and another file with two data
worksheets.

This formula works for 1 of the data worksheets; however,
I want it to look in the other worksheet if it does not
find it in the first one.

=HLOOKUP(D$5,'[Daily Operating Report.xls]Jan-Jun Data'!
$D$6:$F2$41,$C8,FALSE)

D5 is a date and if it is not in the Jan-Jun Data, I want
it to look in the Jul-Dec Data worksheet.

Any help?
 
Hi
one way:
=IF(ISNA(HLOOKUP(D$5,'[Daily Operating Report.xls]Jan-Jun Data'!>
$D$6:$F2$41,$C8,FALSE)),HLOOKUP(D$5,'[Daily Operating
Report.xls]Jul-Dec Data'!> $D$6:$F2$41,$C8,FALSE), HLOOKUP(D$5,'[Daily
Operating Report.xls]Jan-Jun Data'!> $D$6:$F2$41,$C8,FALSE))

This will only return an #NA error if the value cannot be found on both
worksheets

HTH
Frank
 
Hi, Thanks for your help.

I entered the following formula and it returns with an
error:

=IF(ISNA(HLOOKUP(D$5,'[Billing Daily Operating Report.xls]
Jan-Jun Data'!>$D$6:$GH$41,$C8,FALSE)),HLOOKUP
(D$5,'[Billing Daily Operating Report.xls]Jul-Dec Data'!
$D$6:$GH$41,$C8,FALSE),HLOOKUP(D$5,'[Billing Daily
Click to expand...
Operating Report.xls]Jan-Jun Data'!>$D$6:$GH$41,$C8,FALSE))

Do you see anything wrong with this?



-----Original Message-----
Hi
one way:
=IF(ISNA(HLOOKUP(D$5,'[Daily Operating Report.xls]Jan-Jun Data'!>
$D$6:$F2$41,$C8,FALSE)),HLOOKUP(D$5,'[Daily Operating
Report.xls]Jul-Dec Data'!> $D$6:$F2$41,$C8,FALSE), HLOOKUP (D$5,'[Daily
Operating Report.xls]Jan-Jun Data'!> $D$6:$F2 $41,$C8,FALSE))

This will only return an #NA error if the value cannot be found on both
worksheets

HTH
Frank


I have 1 report file and another file with two data
worksheets.

This formula works for 1 of the data worksheets; however,
I want it to look in the other worksheet if it does not
find it in the first one.

=HLOOKUP(D$5,'[Daily Operating Report.xls]Jan-Jun Data'!
$D$6:$F2$41,$C8,FALSE)

D5 is a date and if it is not in the Jan-Jun Data, I want
it to look in the Jul-Dec Data worksheet.

Any help?
Click to expand...


.
Click to expand...
 
Hi

looks like some '<' from the newsreader are inserted. Try the following
=IF(ISNA(HLOOKUP(D$5,'[Billing Daily Operating Report.xls]Jan-Jun
Data'!$D$6:$GH$41,$C8,FALSE)),HLOOKUP(D$5,'[Billing Daily Operating
Report.xls]Jul-Dec Data'!$D$6:$GH$41,$C8,FALSE),HLOOKUP(D$5,'[Billing
Daily Operating Report.xls]Jan-Jun Data'!$D$6:$GH$41,$C8,FALSE))

all in one line (so delete all spaces, etc eventually inserted by your
newsreader)

HTH
Frank


Hi, Thanks for your help.

I entered the following formula and it returns with an
error:

=IF(ISNA(HLOOKUP(D$5,'[Billing Daily Operating Report.xls]
Jan-Jun Data'!>$D$6:$GH$41,$C8,FALSE)),HLOOKUP
(D$5,'[Billing Daily Operating Report.xls]Jul-Dec Data'!
$D$6:$GH$41,$C8,FALSE),HLOOKUP(D$5,'[Billing Daily
Click to expand...
Operating Report.xls]Jan-Jun Data'!>$D$6:$GH$41,$C8,FALSE))

Do you see anything wrong with this?



-----Original Message-----
Hi
one way:
=IF(ISNA(HLOOKUP(D$5,'[Daily Operating Report.xls]Jan-Jun Data'!>
$D$6:$F2$41,$C8,FALSE)),HLOOKUP(D$5,'[Daily Operating
Report.xls]Jul-Dec Data'!> $D$6:$F2$41,$C8,FALSE), HLOOKUP
(D$5,'[Daily Operating Report.xls]Jan-Jun Data'!> $D$6:$F2
$41,$C8,FALSE))

This will only return an #NA error if the value cannot be found on
both worksheets

HTH
Frank


I have 1 report file and another file with two data
worksheets.

This formula works for 1 of the data worksheets; however,
I want it to look in the other worksheet if it does not
find it in the first one.

=HLOOKUP(D$5,'[Daily Operating Report.xls]Jan-Jun Data'!
$D$6:$F2$41,$C8,FALSE)

D5 is a date and if it is not in the Jan-Jun Data, I want
it to look in the Jul-Dec Data worksheet.

Any help?
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top