May 25 2019 11:30 AM
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.
May 25 2019 12:24 PM
May 25 2019 01:52 PM - edited May 25 2019 01:54 PM
SolutionYour 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.
May 25 2019 04:42 PM
T@Detlef Lewin Thanks this works.
May 25 2019 04:43 PM
@Peter BartholomewThanks, learned something new here.
May 25 2019 01:52 PM - edited May 25 2019 01:54 PM
SolutionYour 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.