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?
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,213
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,213
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,213
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!
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,051
Messages
5,509,032
Members
408,701
Latest member
daz457

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top