SOLVED

INDEX MATCH Help!

Copper Contributor

Hi Everyone 

Wonder if someone could help me out :)

I have tried (using google but i think I'm misunderstanding the logic)

 

On Sheet one i have 2 columns 'C' and 'D' are Lists (generated from column A and Row 1 on 2nd sheet (mileage Chart in this post)

Capture.JPG

 

What i want to do is return a value from sheet 2(Mileage Chart) based on the selections of C3 and D3 lists.

My 'Mileage chart is setup as a matrix

Capture44.JPG

 

I have tried the following but can't get it right as you can see from my image i get #Value error!

 

=INDEX('Mileage Chart'!A1:AP43,MATCH(C3&D3,'Mileage Chart'!A1:AP1&'Mileage Chart'!A2:A43,0))

 

Any help would be appreciated

Gary

3 Replies
best response confirmed by gazzo1967 (Copper Contributor)
Solution

@gazzo1967 , you need two MATCH, one for the column and another one for the row, like

=INDEX('Mileage Chart'!A1:AP43,MATCH(C3,'Mileage Chart'!A1:AP1,0),MATCH(D3,'Mileage Chart'!A2:A43,0))

 

@Sergei Baklan 

Thank you for your help :) it makes sense as to why it would not work.

Very much appreciated :)

Gary

Hi Gary - good to know it works finally, you are welcome

 

@gazzo1967 

1 best response

Accepted Solutions
best response confirmed by gazzo1967 (Copper Contributor)
Solution

@gazzo1967 , you need two MATCH, one for the column and another one for the row, like

=INDEX('Mileage Chart'!A1:AP43,MATCH(C3,'Mileage Chart'!A1:AP1,0),MATCH(D3,'Mileage Chart'!A2:A43,0))

 

View solution in original post