INDEX MATCH in a table (multiple lookups)

[surkdidat]

Hi (and thanks, as ever in advance)

In cell E2 (of my look up sheet - named LOOKUP SHEET) I have the name of the column Header (located in Row 1 of my data table (Sheet is called DATA TABLE)

In cell B2 (of my look up sheet - named LOOKUP SHEET) I have the name of the value I want it to look up in the corresponding column above.

The Match I need will be directly to the right of the above. So :

LOOKUP SHEET

B2 - HORSE
E2 - ANIMAL

ANSWER REQUIRED : - 8735

DATA GRID

	Column A	Column B	Column C	Column D	Column E	Column F
Row 1	Colour	Colour(Lookup)	Fruit	Fruit(Lookup)	Animal	Animal(Lookup)
Row 2	Red	4563	Banana	2323	Dog	2443
Row 3	Orange	0025	Pear	4319	Cat	2315
Row 4	Blue	1599	Apple	6533	Horse	8735
Row 5	Green	9315	Cherry	8852	Mouse	1003

 

[Fluff]

Will the value you want to lookup (ie Horse) occur in multiple columns, or multiple rows?

 

[surkdidat]

Will the value you want to lookup (ie Horse) occur in multiple columns, or multiple rows?

Potentially, yes. So it will need to ensure that it matches the row value (for the animal in this case)

Thanks !

(Iam guessing there is an INDEX MATCJ MATCH or something that could do it?

 

[Fluff]

Ok, how about

=INDEX(INDEX(Data!A2:F100,,MATCH(E2,Data!A1:F1,0)+1),MATCH(B2,INDEX(Data!A2:F100,,MATCH(E2,Data!A1:F1,0))))

 

[surkdidat]

Ok, how about

=INDEX(INDEX(Data!A2:F100,,MATCH(E2,Data!A1:F1,0)+1),MATCH(B2,INDEX(Data!A2:F100,,MATCH(E2,Data!A1:F1,0))))

I am getting a #REF! return?

 

[Fluff]

Have you changed the sheet name to match your sheet name?

 

[surkdidat]

Have you changed the sheet name to match your sheet name?

Yes, the tab is named "Data". I have tried it on the master data, which is over a much bigger range, and a test sheet

Have you changed the sheet name to match your sheet name?

Oddly, if i change it the A2:F10 it works. If it is range A2:F100 I get a #N/A on my small sample data

 

[Fluff]

Not sure why that would happen, as it works for me

+Fluff 1.xlsm
	A	B	C	D	E	F
1	Colour	Colour(Lookup)	Fruit	Fruit(Lookup)	Animal	Animal(Lookup)
2	Red	4563	Banana	2323	Dog	2443
3	Orange	25	Pear	4319	Cat	2315
4	Blue	1599	Apple	6533	Horse	8735
5	Green	9315	Cherry	8852	Mouse	1003
6
7
Data

+Fluff 1.xlsm
	A	B	C	D	E	F
1
2		Horse			Animal	8735
Summary

Cell Formulas
Range	Formula
F2	F2	=INDEX(INDEX(Data!A2:F100,,MATCH(E2,Data!A1:F1,0)+1),MATCH(B2,INDEX(Data!A2:F100,,MATCH(E2,Data!A1:F1,0))))

Try using the evaluate formula button on the formulas tab to see where the problem is

 

[surkdidat]

Hi Fluff

Apologies, see what I did wrong now, where I copied my table in, I started the wider formula starting in A1 (in the data sheet) and not A2. Works perfectly now! Thanks for your help!

Not sure why that would happen, as it works for me

+Fluff 1.xlsm
	A	B	C	D	E	F
1	Colour	Colour(Lookup)	Fruit	Fruit(Lookup)	Animal	Animal(Lookup)
2	Red	4563	Banana	2323	Dog	2443
3	Orange	25	Pear	4319	Cat	2315
4	Blue	1599	Apple	6533	Horse	8735
5	Green	9315	Cherry	8852	Mouse	1003
6
7
Data

+Fluff 1.xlsm
	A	B	C	D	E	F
1
2		Horse			Animal	8735
Summary

Cell Formulas
Range	Formula
F2	F2	=INDEX(INDEX(Data!A2:F100,,MATCH(E2,Data!A1:F1,0)+1),MATCH(B2,INDEX(Data!A2:F100,,MATCH(E2,Data!A1:F1,0))))

Try using the evaluate formula button on the formulas tab to see where the problem is

 

[Fluff]

Glad it's sorted & thanks for the feedback.