Index Match Assistance

[harrybillyard9]

Hi,

In the table, I have various figures the headers in C2:F2 are simply 2/2.1/2.2/2.3 i have tried to index match with not much success thus far.

I am just looking for an index match in cell c60 that matches both the configuration in cell c57 and the value in b60 in the table above. As seen currently i keep getting the #N/A

=INDEX(B2:G55, MATCH(C58, B2:B55, 0), MATCH(B60, INDEX(B2:G55, MATCH(C58, B2:B55, 0), 0), 0)) Is the formula that i have been trying to use in the cell

Would appreciate any assistance on this as i have completely hit a wall

 

[KRice]

You will want the formula to be of the form INDEX(full range, row index, column index)...and without setting up a sample worksheet to confirm this works, try:

=INDEX($C$6:$F$35, MATCH($B60, $B$6:$B$35, 0), MATCH(C$58, $C$2:$F$2, 0) )

There are other approaches using the FILTER function if you'd prefer.
This also assumes a match will always be found, otherwise an error will be displayed.

 

[harrybillyard9]

Hi, Than

You will want the formula to be of the form INDEX(full range, row index, column index)...and without setting up a sample worksheet to confirm this works, try:

=INDEX($C$6:$F$35, MATCH($B60, $B$6:$B$35, 0), MATCH(C$58, $C$2:$F$2, 0) )

There are other approaches using the FILTER function if you'd prefer.
This also assumes a match will always be found, otherwise an error will be displayed.

Thanks For the reply i still get an #N/A when inserting this in? I like the filter function but i need to to be empty if there is not a match

 

[KRice]

Are the items to match exactly the same? Extra spaces and hidden characters can cause problems. Try simplifying the B column text...for example, overwrite an upper data table entry with "abc" (without quotes) and enter the same thing in B60. Also confirm that your column headings 2, 2.1, 2.2, and 2.3 have no spaces and at least one is an identical match to the value in C58.

MrExcel_20240716.xlsx
	A	B	C	D	E	F
1
2			2	2.1	2.2	2.3
3
4
5
6		abc	4	8	12	16
7		xyz	18	19	20	21
8		def	66	77	88	99
56
57
58			2.3	2.1	2	2.2
59
60		abc	16	8	4	12
61		def	99	77	66	88
62		xyz	21	19	18	20
63		abc	16	8	4	12
64
Sheet1

Cell Formulas
Range	Formula
C60:F63	C60	=INDEX($C$6:$F$35, MATCH($B60, $B$6:$B$35, 0), MATCH(C$58, $C$2:$F$2, 0) )

 

[harrybillyard9]

Are the items to match exactly the same? Extra spaces and hidden characters can cause problems. Try simplifying the B column text...for example, overwrite an upper data table entry with "abc" (without quotes) and enter the same thing in B60. Also confirm that your column headings 2, 2.1, 2.2, and 2.3 have no spaces and at least one is an identical match to the value in C58.

MrExcel_20240716.xlsx
	A	B	C	D	E	F
1
2			2	2.1	2.2	2.3
3
4
5
6		abc	4	8	12	16
7		xyz	18	19	20	21
8		def	66	77	88	99
56
57
58			2.3	2.1	2	2.2
59
60		abc	16	8	4	12
61		def	99	77	66	88
62		xyz	21	19	18	20
63		abc	16	8	4	12
64
Sheet1

Cell Formulas
Range	Formula
C60:F63	C60	=INDEX($C$6:$F$35, MATCH($B60, $B$6:$B$35, 0), MATCH(C$58, $C$2:$F$2, 0) )

It appears that helped that but then has affected my transpose filter formula =TRANSPOSE(FILTER(Configs!A:A, Configs!B:B = C58))

 

[KRice]

I don't think the C60 formula is affecting your TRANSPOSE formula. Rather, there is a mismatch of some sort. Does Configs!B:B contain text or numbers? Is C58 an exact match to at least one item in Configs!B:B?