Use named range within index match

Solution 1:

INDEX/MATCH is perfectly capable of using Named Ranges that are a table of data. If a 2-D (table) of data is acceptable in the place you use it.

However, you use it in two different places and so need two different things.

In the actual INDEX() portion of the formula, you need first to give it a range to base everything upon. You do, DATA_DUMP_GROCERY being that range. Perfectly fine. DATA_DUMP_GROCERY is presumably a 2-D table of data, so this is exactly what the most common use of INDEX() needs and the function is happy.

However, you then give the same Named Range to MATCH() but it needs a single column to examine. (Actually, there are ways one can make that multiple columns, but not like you did.) Since you gave it a data table with more than one column, it fails. That failure causes the whole thing to fail.

You can use INDEX() inside MATCH() to extract a single column from the Named Range and present that to MATCH(), or you can simply type the single column address reference, or even create another Named Range holding that address.

My preference would be a very simple one: the first thought, using INDEX() inside MATCH():

=INDEX(DATA_DUMP_GROCERY,MATCH(Confectionery!$B$15,
       INDEX( DATA_DUMP_GROCERY,,1  ,  0),4)

Easy since the parameters for that INDEX() are likely simple ands straightforward, eh? No need for anything for ROW since you want all the rows, and whatever column number the lookup data are in (column 1 in the formula above).

Then it will all work very nicely.