Lookup on a Value in different columns

[ASingh95]

Hi,

I have a table on the left which shows a Van and which fruit goes in each van. What I want to do is replicate the right in terms of result, therefore having a list of fruit which I already have but using the table on the left show which van is carrying a particular fruit. (See Link for image)

https://ibb.co/h8Vqwx

Note this is with not much data my actual sheet is far larger with 100s rows and columns.

Thanks in Advance

Aaron

 

[AlanY]

Re: How to do a Lookup on a Value in different columns

try this

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Van	Good1	Good2	Good3	Good4	Good5			Van	Van	Van	Van	Van
2	A	Orange	Bannana	Pear				Apple	B	C	D	E
3	B	Grapes	Apple	Pear				Orange	A	C	D	E
4	C	Orange	Apple	Grapes	Pear			Pear	A	B	C	D
5	D	Apple	Orange	Pear				Grapes	B	C
6	E	Orange	Bannana	Apple				Bannana	A	E
Sheet1

Cell Formulas
Range	Formula
I2	{=IFERROR(INDEX($A$2:$A$6, SMALL(IF(ISNUMBER(MATCH($B$2:$F$6, $H2, 0)), MATCH(ROW($B$2:$B$6), ROW($B$2:$B$6)), ""), COLUMNS($A$1:A1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[ASingh95]

Re: How to do a Lookup on a Value in different columns

Worked like a charm, thank you for your assistance.

 

[AlanY]

Re: How to do a Lookup on a Value in different columns

you're welcome