vlookup maybe

[gman1964]

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.

 

[Dave]

Hi,
If the matching data is only present in one or the other the 2 sheets, (ie,
not both) and if you are using FALSE (or 0) as your 4th argument in your
VLOOKUP's, you could try something like this:

IF(ISNA(VLOOKUP(1st Sheet Lookup Details),VLOOKUP(2nd Sheet Lookup
Details),VLOOKUP(1st Sheet Lookup details))

When using FALSE (or 0) as your 4th argument, VLOOKUP returns a #N/A when
its lookup value is not found.

Regards - Dave.

 

[BoniM]

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.

 

[T. Valko]

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)