vlookup against 2 columns

[codekrush]

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?

 

[mgirvin]

How about something like this:

Excel Workbook
	A	B	C	D	E	F
1	joe	joe	sioux	k		sioux
2	joe	ss	chin	3
3	joe	joe	fred	joe		fred
Sheet1

Excel 2010

Cell Formulas
Range	Formula
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]

Or:

Excel Workbook
	A	B	C	D	E	F
1	joe	joe	sioux	k		sioux
2	joe	ss	chin	3		Criteria not meet
3	joe	joe	fred	joe		fred
Sheet1

Excel 2010

Cell Formulas
Range	Formula
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]

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

 

[mgirvin]

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]

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!