I have three different worksheets and I am trying to use IF and LOOkUP
formulas to search two of the three worksheets for a specific result... I was
just using the LOOKUP function when I had just one worksheet to look at, but
now I want it to search two worksheets and thought using the if with the
lookup but... but the logical test is returning a "false" result even though
it found the correct result.

Sue

From here we can't see your data or your formula.

Could you paste a sample of your data and formula(s) that "don't work"?


Gord Dibben MS Excel MVP

On Fri, 8 Dec 2006 10:04:00 -0800, Sue wrote:

I have three different worksheets and I am trying to use IF and LOOkUP
formulas to search two of the three worksheets for a specific result... I was
just using the LOOKUP function when I had just one worksheet to look at, but
now I want it to search two worksheets and thought using the if with the
lookup but... but the logical test is returning a "false" result even though
it found the correct result.

This does a vlookup for data on sheet 2, if it finds none then it does the
lookup on sheet2.
=IF(ISNA(VLOOKUP(B1,Sheet2!A1:B197,2,FALSE)),VLOOK UP(Sheet1!B1,Sheet3!A1:B26,2,FALSE),VLOOKUP(B1,She et2!A1:B197,2,FALSE))

-John

"Sue" wrote:

I have three different worksheets and I am trying to use IF and LOOkUP
formulas to search two of the three worksheets for a specific result... I was
just using the LOOKUP function when I had just one worksheet to look at, but
now I want it to search two worksheets and thought using the if with the
lookup but... but the logical test is returning a "false" result even though
it found the correct result.

Sue,
Need a little more detail to understand what you're after. Remember that
you must have the data sorted on Sheet2 and Sheet3 in order for Lookup to
work correctly. Have you considered using either vlookup or index/match
instead?

"Sue" wrote in message
...
I have three different worksheets and I am trying to use IF and LOOkUP
formulas to search two of the three worksheets for a specific result... I
was
just using the LOOKUP function when I had just one worksheet to look at,
but
now I want it to search two worksheets and thought using the if with the
lookup but... but the logical test is returning a "false" result even
though
it found the correct result.

Why would info need to be sorted? I have never heard of sorting before a
vlookup. Kind of defeats the purpose.
At any rate the code posted previously works on unsorted data :)
-John

"KC Rippstein" wrote:

Sue,
Need a little more detail to understand what you're after. Remember that
you must have the data sorted on Sheet2 and Sheet3 in order for Lookup to
work correctly. Have you considered using either vlookup or index/match
instead?

"Sue" wrote in message
...
I have three different worksheets and I am trying to use IF and LOOkUP
formulas to search two of the three worksheets for a specific result... I
was
just using the LOOKUP function when I had just one worksheet to look at,
but
now I want it to search two worksheets and thought using the if with the
lookup but... but the logical test is returning a "false" result even
though
it found the correct result.

John

In certain cases and with certain formulas the data needs to be in ascending
order.

The fourth argument in the formula governs this.

VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup)

Range_lookup A logical value that specifies whether you want VLOOKUP to find an
exact match or an approximate match:

If TRUE or omitted, an exact or approximate match is returned. If an exact match
is not found, the next largest value that is less than lookup_value is returned.

The values in the first column of table_array must be placed in ascending sort
order; otherwise, VLOOKUP may not give the correct value. You can put the values
in ascending order by choosing the Sort command from the Data menu and selecting
Ascending. For more information, see Default sort orders.

If FALSE, VLOOKUP will only find an exact match. In this case, the values in the
first column of table_array do not need to be sorted. If there are two or more
values in the first column of table_array that match the lookup_value, the first
value found is used. If an exact match is not found, the error value #N/A is
returned.

Your formula includes FALSE as the fourth argument so sorteing is not required.


Gord Dibben MS Excel MVP


On Fri, 8 Dec 2006 11:03:01 -0800, John Bundy
wrote:

Why would info need to be sorted? I have never heard of sorting before a
vlookup. Kind of defeats the purpose.
At any rate the code posted previously works on unsorted data :)
-John

"KC Rippstein" wrote:

Sue,
Need a little more detail to understand what you're after. Remember that
you must have the data sorted on Sheet2 and Sheet3 in order for Lookup to
work correctly. Have you considered using either vlookup or index/match
instead?

"Sue" wrote in message
...
I have three different worksheets and I am trying to use IF and LOOkUP
formulas to search two of the three worksheets for a specific result... I
was
just using the LOOKUP function when I had just one worksheet to look at,
but
now I want it to search two worksheets and thought using the if with the
lookup but... but the logical test is returning a "false" result even
though
it found the correct result.

Hi
I guess one can do it this way to lookup multiple sheets

sheet1

AB1001 200
AB1002 300

sheet2

BC1001 500
BC1002 400

sheet3

CD1001 600
CD1002 700

The formula will be

=IF(OR(sheet1!A1:A2=A1),VLOOKUP(A1,sheet1!A1:B2,2, FALSE),IF(OR(sheet2!A1:B1=A1),VLOOKUP(A1,sheet2!A1 :B2,2,FALSE),IF(OR(sheet3!A1:A2=A1),VLOOKUP(A1,she et3!A1:B2,2,FALSE),"")))

Hope this helps.

Thanks,
Shail


Gord Dibben wrote:
Sue

From here we can't see your data or your formula.

Could you paste a sample of your data and formula(s) that "don't work"?


Gord Dibben MS Excel MVP

On Fri, 8 Dec 2006 10:04:00 -0800, Sue wrote:

I have three different worksheets and I am trying to use IF and LOOkUP
formulas to search two of the three worksheets for a specific result... I was
just using the LOOKUP function when I had just one worksheet to look at, but
now I want it to search two worksheets and thought using the if with the
lookup but... but the logical test is returning a "false" result even though
it found the correct result.

Please enter this pressing <CTRL<SHIFT<ENTER together

shail wrote:
Hi
I guess one can do it this way to lookup multiple sheets

sheet1

AB1001 200
AB1002 300

sheet2

BC1001 500
BC1002 400

sheet3

CD1001 600
CD1002 700

The formula will be

=IF(OR(sheet1!A1:A2=A1),VLOOKUP(A1,sheet1!A1:B2,2, FALSE),IF(OR(sheet2!A1:B1=A1),VLOOKUP(A1,sheet2!A1 :B2,2,FALSE),IF(OR(sheet3!A1:A2=A1),VLOOKUP(A1,she et3!A1:B2,2,FALSE),"")))

Hope this helps.

Thanks,
Shail


Gord Dibben wrote:
Sue

From here we can't see your data or your formula.

Could you paste a sample of your data and formula(s) that "don't work"?


Gord Dibben MS Excel MVP

On Fri, 8 Dec 2006 10:04:00 -0800, Sue wrote:

I have three different worksheets and I am trying to use IF and LOOkUP
formulas to search two of the three worksheets for a specific result... I was
just using the LOOKUP function when I had just one worksheet to look at, but
now I want it to search two worksheets and thought using the if with the
lookup but... but the logical test is returning a "false" result even though
it found the correct result.