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!

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"thecdnmole" wrote in message
...
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!

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.

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"thecdnmole" wrote in message
...
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!

How about something like this, in say Sheet3's B1:
=IF(ISNA(VLOOKUP(A1,Sheet1!A:B,2,0)),IF(ISNA(VLOOK UP(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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"thecdnmole" wrote:
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.

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

"Max" wrote:

How about something like this, in say Sheet3's B1:
=IF(ISNA(VLOOKUP(A1,Sheet1!A:B,2,0)),IF(ISNA(VLOOK UP(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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"thecdnmole" wrote:
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.

IT WORKS!!! Thank-you!!!

"Max" wrote:

How about something like this, in say Sheet3's B1:
=IF(ISNA(VLOOKUP(A1,Sheet1!A:B,2,0)),IF(ISNA(VLOOK UP(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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"thecdnmole" wrote:
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.

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" wrote:

How about something like this, in say Sheet3's B1:
=IF(ISNA(VLOOKUP(A1,Sheet1!A:B,2,0)),IF(ISNA(VLOOK UP(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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"thecdnmole" wrote:
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.

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"thecdnmole" wrote in message
...
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?

Aha, you got it! Welcome.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"thecdnmole" wrote in message
...
IT WORKS!!! Thank-you!!!

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.

"Max" wrote:

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"thecdnmole" wrote in message
...
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?

Got it working, thanks again!!

"Max" wrote:

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"thecdnmole" wrote in message
...
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?