Help with VLOOKUP multiple columns formula

[Arinoum]

Hi,
I am trying to write a formula that looks up a value in several columns and if it matches give the result from the column next to it. The formula goes in column A and should be something like this: If B2=a value found in columns C, E or G then give the result that's in the column next to it which should be columns D,F or H. This is what I have so far but I know it's wrong =If(b1=vlookup(b1,C:H,{3,5,7} then give the result shown in the same row as the value but on the next column which should be columns 4,6,8. Based on the below table the result for the first row should be 1 and for the second should be 0.
Please help! Thank you.

Formula	A120	A129	33	A120	1	A121	2
Formula	A122	A122	0	A177	12	A123	1

 

[richh]

You probably want to check out INDEX MATCH

How to use INDEX and MATCH

INDEX and MATCH is the most popular tool in Excel for performing more advanced lookups. This is because INDEX and MATCH are incredibly flexible – you can do horizontal and vertical lookups, 2-way lookups, left lookups, case-sensitive lookups, and even lookups based on multiple criteria. If you...

exceljet.net

 

[Eric W]

If you're returning a numeric value, it's pretty easy:

Book1
	A	B	C	D	E	F	G	H
1
2	1	A120	A129	33	A120	1	A121	2
3	0	A122	A122	0	A177	12	A123	1
4	33	A129
5
Sheet19

Cell Formulas
Range	Formula
A2:A4	A2	=SUMIF($C$2:$G$3,B2,$D$2:$H$3)

If you're returning text, it's a bit tougher.

 

[Arinoum]

If you're returning a numeric value, it's pretty easy:

Book1
	A	B	C	D	E	F	G	H
1
2	1	A120	A129	33	A120	1	A121	2
3	0	A122	A122	0	A177	12	A123	1
4	33	A129
5
Sheet19

Cell Formulas
Range	Formula
A2:A4	A2	=SUMIF($C$2:$G$3,B2,$D$2:$H$3)

If you're returning text, it's a bit tougher.

It's not always numerical. There are some letters and numbers in the return value.

 

[Arinoum]

You probably want to check out INDEX MATCH

How to use INDEX and MATCH

INDEX and MATCH is the most popular tool in Excel for performing more advanced lookups. This is because INDEX and MATCH are incredibly flexible – you can do horizontal and vertical lookups, 2-way lookups, left lookups, case-sensitive lookups, and even lookups based on multiple criteria. If you...

exceljet.net

From what I've seen I can match one column at a time. I have several columns and they are not all in the same range. I'm not sure how to use this formula in my case.

 

[Eric W]

It's not always numerical. There are some letters and numbers in the return value.

In that case, here's another option:

Book1
	A	B	C	D	E	F	G	H
1
2	1	A120	A129	33	A120	1	A121	testing
3	0	A122	A122	0	A177	12	A123	1
4	33	A129
5	testing	A121
6
Sheet19

Cell Formulas
Range	Formula
A2:A5	A2	=IFERROR(INDIRECT(TEXT(MIN(IF(($C$2:$G$3=B2)*(MOD(COLUMN($C$2:$G$3)-COLUMN($C$2),2)=0),ROW($C$2:$G$3)*100+COLUMN($C$2:$G$3)+1)),"R00C00"),0),"No match")
Press CTRL+SHIFT+ENTER to enter array formulas.

If you have Excel 365 (you might want to update your member profile), I might be able to come up with something a bit simpler.

 

[Arinoum]

In that case, here's another option:

Book1
	A	B	C	D	E	F	G	H
1
2	1	A120	A129	33	A120	1	A121	testing
3	0	A122	A122	0	A177	12	A123	1
4	33	A129
5	testing	A121
6
Sheet19

Cell Formulas
Range	Formula
A2:A5	A2	=IFERROR(INDIRECT(TEXT(MIN(IF(($C$2:$G$3=B2)*(MOD(COLUMN($C$2:$G$3)-COLUMN($C$2),2)=0),ROW($C$2:$G$3)*100+COLUMN($C$2:$G$3)+1)),"R00C00"),0),"No match")
Press CTRL+SHIFT+ENTER to enter array formulas.

If you have Excel 365 (you might want to update your member profile), I might be able to come up with something a bit simpler.

It worked! Thank you so much!

 

[Eric W]

Happy to help!