SOLVED
Home

Index Match Match - wrong value returned

%3CLINGO-SUB%20id%3D%22lingo-sub-646129%22%20slang%3D%22en-US%22%3EIndex%20Match%20Match%20-%20wrong%20value%20returned%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-646129%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20a%20simple%20INDEX%2FMatch%202%20way%20lookup%20that%20I%20had%20working%20before%20but%20is%20giving%20wrong%20values.%3C%2FP%3E%3CP%3EUsed%20manually%20created%20table%20before%20but%20in%20this%20case%20used%20Excel%20table%20to%20make%20the%20list%20dynamic.%3C%2FP%3E%3CP%3EIts%20a%20coffee%20selection%20menu%20with%20data%20validation%20to%20select%20size%20and%20coffee%20type%20and%20formula%20in%20C27%20gives%20the%20amount.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDEX(Table1%2CMATCH(%24A%2429%2CTblMenu%2C0)%2CMATCH(%24A%2426%2CTblHdr%2C0))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20how%20would%20you%20make%20the%20data%20validation%20source%20part%20dynamic.%20It%20won't%20accept%20range%20name%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJay.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-646129%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-646154%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20Match%20-%20wrong%20value%20returned%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-646154%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F330883%22%20target%3D%22_blank%22%3E%40AVP68%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMinor%20adjustment.%3C%2FP%3E%3CPRE%3E%3DINDEX(Table1%2CMATCH(%24A%2429%2CTblMenu%2C0)-1%2CMATCH(%24A%2426%2CTblHdr%2C0))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-646159%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20Match%20-%20wrong%20value%20returned%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-646159%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F330883%22%20target%3D%22_blank%22%3E%40AVP68%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20problem%20was%20that%20your%20range%20of%20menu%20lookup%20and%20your%20definition%20to%20Table1%20did%20not%20correspond.%20Once%20you%20have%20defined%20a%20table%20any%20names%20you%20need%20for%20validation%20or%20to%20identify%20parts%20of%20the%20table%20can%20be%20defined%20in%20terms%20of%20the%20structured%20references.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20used%20'AvailableSize'%20both%20for%20validation%20and%20in%20the%20formula%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20INDEX(%20DrinksPrices%2C%20%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EMATCH(%20Drink%2C%20Menu%2C%200%20)%2C%20%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EMATCH(%20Size%2C%20AvailableSize%2C%200%20)%2B1%20)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ewhere%20'AvailableSize'%20is%20defined%20as%20part%20of%20the%20header%20row.%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CSPAN%3E%3D%20DrinksPrices%5B%5B%23Headers%5D%2C%5BTALL%5D%3A%5BVENTI%5D%5D%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESimilarly%20'Menu'%2C%20which%20refers%20to%20the%20data%20field%2C%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20DrinksPrices%5BCLASSICS%2FFAVOURITES%5D%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eis%20used%20for%20both%20purposes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20table%20'DrinksPrices'%20refers%20to%20the%20data%20only%20and%20does%20not%20include%20the%20header%20row%20nor%20any%20totals%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-646267%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20Match%20-%20wrong%20value%20returned%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-646267%22%20slang%3D%22en-US%22%3E%3CP%3ET%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%20Thanks%20this%20works.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-646268%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20Match%20-%20wrong%20value%20returned%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-646268%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3EThanks%2C%20learned%20something%20new%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E
AVP68
Occasional Contributor

Hi All,

 

This is a simple INDEX/Match 2 way lookup that I had working before but is giving wrong values.

Used manually created table before but in this case used Excel table to make the list dynamic.

Its a coffee selection menu with data validation to select size and coffee type and formula in C27 gives the amount.

 

=INDEX(Table1,MATCH($A$29,TblMenu,0),MATCH($A$26,TblHdr,0))

 

Also, how would you make the data validation source part dynamic. It won't accept range name?

 

Please see attached.

 

Thanks,

 

Jay.

 

 

 

 

 

 

4 Replies

@AVP68 

Minor adjustment.

=INDEX(Table1,MATCH($A$29,TblMenu,0)-1,MATCH($A$26,TblHdr,0))

 

Solution

@AVP68 

Your problem was that your range of menu lookup and your definition to Table1 did not correspond. Once you have defined a table any names you need for validation or to identify parts of the table can be defined in terms of the structured references.

 

I have used 'AvailableSize' both for validation and in the formula

= INDEX( DrinksPrices,

MATCH( Drink, Menu, 0 ),

MATCH( Size, AvailableSize, 0 )+1 )

where 'AvailableSize' is defined as part of the header row.

= DrinksPrices[[#Headers],[TALL]:[VENTI]]

 

Similarly 'Menu', which refers to the data field,

= DrinksPrices[CLASSICS/FAVOURITES]

is used for both purposes.

 

The table 'DrinksPrices' refers to the data only and does not include the header row nor any totals row.

 

T@Detlef Lewin Thanks this works.

@Peter BartholomewThanks, learned something new here.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
217 Replies