LOOKUP requires the lookup_vector be sorted in ascending order.

Try this:

=VLOOKUP(A1,IF(COUNTIF(Sheet2!A1:A5,A1),Sheet2!A1: B5,Sheet3!A1:B5),2,0)

--
Biff
Microsoft Excel MVP


"BoniM" wrote in message
...
If the value is definitly on one of the two sheets and only on one of the
two
sheets - this formula has no provision for a value with no match and will
return #N/A in that case...

=VLOOKUP(A2,IF(LOOKUP(A2,DataSheet!$A$2:$A$30)=A2, DataSheet!$A$2:$B$30,DataSheet2!$A$2:$B$30),2,0)

where A2 contains the value to lookup...
if lookup finds a matching value in the first column of data in the first
data sheet:
LOOKUP(A2,DataSheet!$A$2:$A$26)=A2
then the table for lookup would be that sheet:
DataSheet!$A$2:$B$26
if not, it will be the second sheet:
DataSheet2!$A$2:$B$30
In this example, returning data from the second column when there is an
exact match...
substitute as necessary.


"gman1964" wrote:

I have a spreadsheet that has data in a column of cells that have both
numbers and letters (ABCD 123456) that shoud match data in one of two
tabs in
the same worksheet. What I'm trying to do is have a forumla look at the
information in the first tab match it to the same matching information in
the
other tabs and depending on which of the two tabs in finds the
information in
will return a answer. I've been playing with the vlookup and can get a
formula to look at one sheet and return the right answer, but I can't get
a
formula to look at both sheets and return the right answer.