SOLVED
Home

INDEX MATCH Help!

%3CLINGO-SUB%20id%3D%22lingo-sub-444579%22%20slang%3D%22en-US%22%3EINDEX%20MATCH%20Help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-444579%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Everyone%26nbsp%3B%3C%2FP%3E%3CP%3EWonder%20if%20someone%20could%20help%20me%20out%20%3A)%3C%2FP%3E%3CP%3EI%20have%20tried%20(using%20google%20but%20i%20think%20I'm%20misunderstanding%20the%20logic)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20Sheet%20one%20i%20have%202%20columns%20'C'%20and%20'D'%20are%20Lists%20(generated%20from%20column%20A%20and%20Row%201%20on%202nd%20sheet%20(mileage%20Chart%20in%20this%20post)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20446px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F108426iC2EA513DA77AC25D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture.JPG%22%20title%3D%22Capture.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20i%20want%20to%20do%20is%20return%20a%20value%20from%20sheet%202(Mileage%20Chart)%20based%20on%20the%20selections%20of%20C3%20and%20D3%20lists.%3C%2FP%3E%3CP%3EMy%20'Mileage%20chart%20is%20setup%20as%20a%20matrix%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F108427iE770EC51BB8CCCB0%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22Capture44.JPG%22%20title%3D%22Capture44.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20the%20following%20but%20can't%20get%20it%20right%20as%20you%20can%20see%20from%20my%20image%20i%20get%20%23Value%20error!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DINDEX('Mileage%20Chart'!A1%3AAP43%2CMATCH(C3%26amp%3BD3%2C'Mileage%20Chart'!A1%3AAP1%26amp%3B'Mileage%20Chart'!A2%3AA43%2C0))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20appreciated%3C%2FP%3E%3CP%3EGary%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-444579%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-444677%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%20MATCH%20Help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-444677%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F320046%22%20target%3D%22_blank%22%3E%40gazzo1967%3C%2FA%3E%26nbsp%3B%2C%20you%20need%20two%20MATCH%2C%20one%20for%20the%20column%20and%20another%20one%20for%20the%20row%2C%20like%3C%2FP%3E%0A%3CPRE%3E%3DINDEX('Mileage%20Chart'!A1%3AAP43%2CMATCH(C3%2C'Mileage%20Chart'!A1%3AAP1%2C0)%2CMATCH(D3%2C'Mileage%20Chart'!A2%3AA43%2C0))%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-449313%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%20MATCH%20Help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-449313%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help%20%3A)%20it%20makes%20sense%20as%20to%20why%20it%20would%20not%20work.%3C%2FP%3E%3CP%3EVery%20much%20appreciated%20%3A)%3C%2FP%3E%3CP%3EGary%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-449483%22%20slang%3D%22en-US%22%3ERe%3A%20INDEX%20MATCH%20Help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-449483%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Gary%20-%20good%20to%20know%20it%20works%20finally%2C%20you%20are%20welcome%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F320046%22%20target%3D%22_blank%22%3E%40gazzo1967%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
gazzo1967
New 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
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 

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies