If and VLOOKUP Query

[Guest]

Hi guys, I am trying to use an IF statement with a VLOOKUP function to return
data based on one of two criteris ,, e.g. column A is where I want to place
the formula, column B is the first criteria, column C is the 2nd criteria
,,,, what I want to see in column A is the result from doing a VLOOKUP on
column B ( using a matrix ) and if the return data is "Unknown" then VLOOKUP
column C and return the result form the same matrix ,,,, i.e.
If(VLOOKUP(B1,Matrix,2,0)="Unknown",Vlookup(C1,matrix,2,0) ...... anyone know
of an easy solution?
A B C
nuts Bolts

 

[T. Valko]

You have the right idea. You just need to finish it:

=IF(VLOOKUP(B1,Matrix,2,0)="Unknown",VLOOKUP(C1,Matrix,2,0),VLOOKUP(B1,Matrix,2,0))--BiffMicrosoft Excel MVP"John Moore" <[email protected]> wrote in messageHi guys, I am trying to use an IF statement with a VLOOKUP function toreturn> data based on one of two criteris ,, e.g. column A is where I want toplace> the formula, column B is the first criteria, column C is the 2nd criteria> ,,,, what I want to see in column A is the result from doing a VLOOKUP on> column B ( using a matrix ) and if the return data is "Unknown" thenVLOOKUP> column C and return the result form the same matrix ,,,, i.e.> If(VLOOKUP(B1,Matrix,2,0)="Unknown",Vlookup(C1,matrix,2,0) ...... anyoneknow> of an easy solution?> A B C> nuts Bolts

 

[T. Valko]

Yikes! I have no idea how that reply got all jammed together like that. Here
it is again:

You have the right idea. You just need to finish it:

=IF(VLOOKUP(B1,Matrix,2,0)="Unknown",VLOOKUP(C1,Matrix,2,0),VLOOKUP(B1,Matrix,2,0))--

 

[Guest]

you can use either countif or isna to determine if your vlookup is going to
find a value so in general here is the theory of what you want

if(countif(A1:A100, "Apple")<>0, vlookup("apple", A1100, 4, false),
if(countif(B1:B100, "Orange") <> 0, Vlookup("Orange", B1100, 3, False),
"NA"))

ISNA would be similar but in general
if(isna(vlookup(...), Not found, vlookup(...))

 

[Guest]

this ..?

=IF(ISNA(VLOOKUP(B1,matrix,2,0)),IF(ISNA(VLOOKUP(C1,matrix,2,0)),"Unknown",VLOOKUP(C1,matrix,2,0)),VLOOKUP(B1,matrix,2,0))