Finding intersection value of specific row and column that match a certain value

I think for this you need to make each airline's table a named range and then use a mixture of LOOKUP, CHOOSE, VLOOKUP or INDEX MATCH with INDIRECT. I don't immediately see any need for the OFFSET function. I'm about to log off, but will look in tomorrow to see if it has been solved.

Have a look at the attached, which I have thrown together quickly. I think it does what you want.

As per Ali's post #4, Ali created "Named ranges" for each Airline.

As you cannot have a blank in the name of a "named range" Ali inserted an underscore "-" so "Air China" has named range of "Air_China" and the class is named "AIR_CHINA_class". In all instances the named range (such as "AIR_CHINA") references the columns headed M&M, Bonus and Smiles

Ali's formula:

=INDEX(INDIRECT(SUBSTITUTE($C$21," ","_")),MATCH($C$22,INDIRECT(SUBSTITUTE($C$21," ","_")&"_class"),0),1)

SUBSTITUTE($C$21," ","_") replaces the blank in the name in C21 with an undescore which gives the name of a named range.

The INDIRECT is the way the named range is referenced so for C21="AIR CHINA" we get INDIRECT("AIR_CHINA"): the range used by INDEX

MATCH($C$22,INDIRECT(SUBSTITUTE($C$21," ","_")&"_class"),0),1)

INDEX requires a row and/or column reference so ...

we match C22 against another range e.g "AIR_CHINA_Class" to get the row number.

The column number of the INDEX range (e.g. AIR_CHINA) is highlighted in bold red so 1=M&M, 2= Bonus and 3=Smiles.

An excellent solution from Ali !

Hope this explains how this solution works.


Just as an aside:

The named ranges such as "AIR_CHINA" could have included the class (4 columns) rather than a separate "class" named range and the following formula used:


=VLOOKUP($C$22,SUBSTITUTE($C$21," ","_")),2,0) for "M&M"

=VLOOKUP($C$22,SUBSTITUTE($C$21," ","_")),3,0) for "Bonus"

=VLOOKUP($C$22,SUBSTITUTE($C$21," ","_")),4,0) for "Smiles"

In the above, VLOOKUP (always) finds a match with C22 in the first column of the (any) range and then returns the result from the column specified in red

@John - thanks for the detailed explanation, and yes, of course VLOOKUP would have avoided the extra named ranges!

@Nikox - if you go to the Formulas ribbon and open the named ranges manager you will see the arrays that I set up, and the formulae use these named ranges based on what appears in the drop-down in cell C21. I did not actually reference the cell with the airline's name at all, having already defined the ranges here so that Excel knows where to look.

Have a good look at it, step through the formulae using the evaluate option and, if you have any further questions, do please feel free to ask. Mastering the use of INDIRECT and named ranges can save you a lot of time, so I'd recommend you try to fully understand what the formulae are doing, but John has given you a pretty thorough explanation above.

Glad to have helped!