SOLVED

Index Match Match - wrong value returned

Copper 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))

 

best response confirmed by AVP68 (Copper Contributor)
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.

1 best response

Accepted Solutions
best response confirmed by AVP68 (Copper Contributor)
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.

 

View solution in original post