vlookup with if statement

blackduck

New Member
Joined
Sep 27, 2017
Messages
7
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:
[TABLE="width: 303"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[TD]RETURNED VALUES[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]d[/TD]
[TD]blue[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]e[/TD]
[TD]red[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]f[/TD]
[TD]yellow[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]d[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]e[/TD]
[TD]orange[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]f[/TD]
[TD]black


[/TD]
[/TR]
</tbody>[/TABLE]

LOOKUP TABLE:
[TABLE="width: 281"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]blue[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]red[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]yellow[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]d[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]e[/TD]
[TD]orange[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]f[/TD]
[TD]black

[/TD]
[/TR]
</tbody>[/TABLE]



THANK YOU SO MUCH FOR YOUR HELP!!
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Hi Black,

Try

Code:
=IFNA(VLOOKUP(A2,A10:C15,3,0),VLOOKUP(B2,B10:C15,2,0))
 
Upvote 0
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'
 
Upvote 0
This worked for me;

Code:
=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.
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,312
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top