Vlookup

[thecdnmole]

I have two worksheets, same format, different data. Let's say for simplicity
worksheet one has numbers from 1 to 100 and worksheet 2 from 101 to 200. On
a third worksheet I want to have a search feature, so that if I am looking
for number 20 it looks for it on both sheets, another example would be 150,
it needs to seach both worksheets and provide an answer in the cell. Thansk!

 

[Don Guillett]

Is there more than one answer possible, such as one on each sheet?

 

[thecdnmole]

No, if I am looking for 150, it wouldn't be on the first worksheet with
numbers from 1 - 100, but I don't know that from my search page. Also the
numbers are randon, not in order.

 

[Max]

How about something like this, in say Sheet3's B1
=IF(ISNA(VLOOKUP(A1,Sheet1!A:B,2,0)),IF(ISNA(VLOOKUP(A1,Sheet2!A:B,2,0)),"",VLOOKUP(A2,Sheet2!A:B,2,0)),VLOOKUP(A1,Sheet1!A:B,2,0))

Above looks A1 up in Sheet1, then in Sheet2

 

[thecdnmole]

Thanks, will this lookup the whole column of numbers from 1-100 and 101-200
or just what's in cell A1?

 

[thecdnmole]

IT WORKS!!! Thank-you!!!

How about something like this, in say Sheet3's B1:
=IF(ISNA(VLOOKUP(A1,Sheet1!A:B,2,0)),IF(ISNA(VLOOKUP(A1,Sheet2!A:B,2,0)),"",VLOOKUP(A2,Sheet2!A:B,2,0)),VLOOKUP(A1,Sheet1!A:B,2,0))

Above looks A1 up in Sheet1, then in Sheet2

 

[thecdnmole]

I have one column that is left of the balance of the vlookup colkumns and
used index to find the answer, can that be done similar to what you did with
the vlookup on two sheets, but left side lookup?

 

[Max]

Yes, of course, it'll work just as well with the more versatile INDEX/MATCH

An INDEX/MATCH example along the same lines as the earlier:
=IF(ISNA(MATCH(A1,Sheet1!B:B,0)),IF(ISNA(MATCH(A1,Sheet2!B:B,0)),"",INDEX(Sheet2!A:A,MATCH(A2,Sheet2!B:B,0))),INDEX(Sheet1!A:A,MATCH(A2,Sheet1!B:B,0)))

Above tries matching A1 in Sheet1's col B first, then IF no match is found,
it'll try in Sheet2's col B.

 

[Max]

Aha, you got it! Welcome.

 

[thecdnmole]

This one is giving me trouble. You have B:B and also A:A. I want to search
column C and return the value in column A that matches the seach in C. I got
it to return the same valu in the column I searched (C), but I can't get it
to return the value in A.

 

[thecdnmole]

Got it working, thanks again!!

Yes, of course, it'll work just as well with the more versatile INDEX/MATCH

An INDEX/MATCH example along the same lines as the earlier:
=IF(ISNA(MATCH(A1,Sheet1!B:B,0)),IF(ISNA(MATCH(A1,Sheet2!B:B,0)),"",INDEX(Sheet2!A:A,MATCH(A2,Sheet2!B:B,0))),INDEX(Sheet1!A:A,MATCH(A2,Sheet1!B:B,0)))

Above tries matching A1 in Sheet1's col B first, then IF no match is found,
it'll try in Sheet2's col B.