Searching worksheets in a specific order

[sew2009]

Thanks to all for this forum. It has been a valuable resource for me.
I have three sheets of data that I need to search for data. I want to search them in a specific order (abcsale, abcsoc, abc) and return the results from the first one that matches. Here is the formula I am using:
=IF(ISNA(VLOOKUP(--B3,ABCSALE!$B:$F,5,FALSE)),IF(ISNA(VLOOKUP(--B3,ABCSOC!$B:$G,6,FALSE)),IF(ISNA(VLOOKUP(--B3,ABC!$B:$G,6,FALSE)),VLOOKUP(--B3,ABC!$B:$G,6,FALSE)),VLOOKUP(--B3,ABCSOC!$B:$G,6,FALSE)),VLOOKUP(--B3,ABCSALE!$B:$F,5,FALSE))
Th problem is that it returns the correct value if it appears in one of the first two worksheets (abcsale, abcsoc) but if the value is only in the last checked worksheet (abc) then it displays "FALSE."
I'm sure this is a ridiculously simple issue, but I can't find the answer through my searches. Let me know if it would be helpful to include the workbook itself.

[DonkeyOte]

I think you're missing a Null etc

Please Login or Register  to view this content.

or something along those lines

If the data type being returned is consistent you could also use a LOOKUP based approach but it could be deemed less efficient than the above depending upon how likely you are to get match in first lookup.

[sew2009]

Thanks! That was driving me crazy, but I couldn't see the forest through the trees.
I didn't try LOOKUP first; the first two sheets are small (consisting of sale items, and special order inventory) but change monthly, so I wanted to make sure that my prices were accurate without manually entering them all.

[DonkeyOte]

What I meant re: LOOKUP is this - if we assume the value being returned by the various VLOOKUPs is always a number (if successful) then you can avoid the double evaluation:

Please Login or Register  to view this content.

Note how the VLOOKUPs are now listed in reverse order.
This is because the LOOKUP construct (as used here) will return the last number found in the 4 value array.
As such you want the most desired result listed last and least desired listed first.

The key difference between the two approaches (embedded IF with double evaluation and LOOKUP) is that all 3 VLOOKUPs are performed at all times in the LOOKUP approach (though each is performed once only)

This is the point I alluded to regards whether or not the majority of your VLOOKUP1 tests will be successful ?

If they are then given the present embedded IF approach you're only performing 2 VLOOKUPs, using the LOOKUP you're performing 3.

Conversely if you expect the majority of VLOOKUP1 tests to fail then the above is likely to prove more efficient.
This is because a VLOOKUP1 failure would result in at least 3 VLOOKUPs being performed via the embedded IF route (VLOOKUP1, VLOOKUP2*2).

Should VLOOKUP2 also fail then you're performing 5 VLOOKUPs using the embedded IF route (VLOOKUP1, VLOOKUP2*2, VLOOKUP3*2)

The downside to the LOOKUP is that if each VLOOKUP returns more than one data type (ie numbers and strings) then it won't work.