vlookup with if statement

[blackduck]

hi, I need some help with a vlookup, any help would be greatly appreciated!

I need to look at column 1 and return the value specified in the lookup table, but if the number doesn't exist in column 1 lookup table, i want to lookup column 2 and return the value based on the lookup table. Any ideas how i write this?

EXAMPLE DATA with last column being what i want to be generated by the formula:

Column 1	Column 2	RETURNED VALUES
1	d	blue
2	e	red
3	f	yellow
4	d	green
5	e	orange
6	f	black

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

LOOKUP TABLE:

Column 1	Column 2	Result
1		blue
2		red
3		yellow
	d	green
	e	orange
	f	black

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

THANK YOU SO MUCH FOR YOUR HELP!!

 

[RasGhul]

Hi Black,

Try

=IFNA(VLOOKUP(A2,A10:C15,3,0),VLOOKUP(B2,B10:C15,2,0))

 

[blackduck]

PERFECT - thank you so much!!! One last question, how do i adjust the formula so if the thing i am looking up is not in either lookup column then it shows returns a particular work e.g. 'PINK'

 

[RasGhul]

This worked for me;

=IFNA(IFNA(VLOOKUP(A2,$A$11:$C$18,3,0),VLOOKUP(B2,$B$11:$C$18,2,0)),"PINK")

** Note that the lookup ranges are now locked eg $A$11:$C$18, remember to increase the range when you add more data.