vlookup against 2 columns

codekrush

New Member
Joined
Apr 29, 2010
Messages
24
Hey i am wondering if you can use vlookup to validate criteria in either 1 of 2 columns.

IE:

Cell A1 has the serch critera i want to validate that this content is in either column B or D then return the value in column C if it is in EITER column B or D.

Is vlookup capable of doing this or will i have to use match with an aray?
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,216
How about something like this:
Excel Workbook
ABCDEF
1joejoesiouxksioux
2joesschin3
3joejoefredjoefred
Sheet1
Excel 2010
Cell Formulas
RangeFormula
F1=IF(OR(A1=B1,A1=D1),C1,"")
F2=IF(OR(A2=B2,A2=D2),C2,"")
F3=IF(OR(A3=B3,A3=D3),C3,"")
 

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,216
Or:
Excel Workbook
ABCDEF
1joejoesiouxksioux
2joesschin3Criteria not meet
3joejoefredjoefred
Sheet1
Excel 2010
Cell Formulas
RangeFormula
F1=IF(OR(A1=B1,A1=D1),C1,"Criteria not meet")
F2=IF(OR(A2=B2,A2=D2),C2,"Criteria not meet")
F3=IF(OR(A3=B3,A3=D3),C3,"Criteria not meet")
 

codekrush

New Member
Joined
Apr 29, 2010
Messages
24
Looks like that will work, was hoping to shy away from the if statemant due to the fact that it will give me a rather long nested if's but thankyou for the help:cool:
 

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,216
Maybe I did not understand how your data is set up? If it is just checking A against B & D, then then IF formula should not need any more nested IFs. Let me know how the dat is set up.
 

codekrush

New Member
Joined
Apr 29, 2010
Messages
24
I was actually trying to assist a friend and didn't have a good hold on what they were lookin for; in the long run i came up with this:

=IF(ISNUMBER(H4),VLOOKUP($H4,Materials!$B$4:$D$19,3,FALSE),IF(ISTEXT(H4),VLOOKUP($H4,Materials!$C$4:$D$19,2,FALSE),0))


i isn't perfect for an exact match but that was not nessesary, it was easier to do this than explain and educate on match and arrays.. TY for your help... I love this board!
 

Watch MrExcel Video

Forum statistics

Threads
1,132,867
Messages
5,655,700
Members
418,230
Latest member
Jimmy_Jef

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
Top