Get value of cell in current row by column title.

Question

Level 1

0 points

Get value of cell in current row by column title.

I have limited knowledge of INDEX and MATCH type functions, but can't seem to figure this one out.

I'm writing a sheet to keep track of the value of my airline upgrades by tracking the costs of Main Cabin, Economy+ and First for each flight I purchase. I then want to turn that around and figure out the benefit of my free upgrades (because of my medallion status with the airline).

I've outlined everything a little more clearly in the attached image. The Paid For and Flew columns are drop-down menus with matching values to the header names of the different fare types.

Any help is massively appreciated. I've been trying different ideas for an hour and am at the end of my wits :)

Posted on Feb 13, 2025 3:38 PM

Reply

Answer 1

Top-ranking reply

Camelot

Level 9

58,299 points

Feb 13, 2025 3:59 PM in response to Drew Cain

There are several ways of going about this. Since there are only three options, an IF() would work, but I prefer to use INDIRECT()

INDIRECT() lets you build a cell reference using row and column coordinates. Since you know the row number (it's the current row), all you have to do is find the column number, which a simple MATCH() can do.

To find the column that matches the Paid For cell, I use:

=MATCH(D2,$1:$1,0)

This looks up the value of D2 in the header row and returns an index matching the column number - either 1, 2 or 3, depending on the choice. This result can can be passed to INDIRECT() to perform the full lookup.

Assuming your columns in your screenshot start at A, set F2 to:

=INDIRECT("R"& ROW()&"C"&MATCH(D2,$1:$1,0),0)

Breaking this down, I'm passing INDIRECT() a string consisting of "R" followed by the current row number (i.e. 2), followed by "C" and the result of the MATCH() from above. The 0 on the end tells it to use RC-style references rather than normal A1-style.

That ends up with something like:

=INDIRECT("R2C1",0)

and returns the value of the cell in the first column of the second row (the 'Main' ticket cost).

The same function can be applied to the Flew Cost function, where the MATCH() looks for E2 rather than D2

Both functions can fill down the column to fill out the table.

Reply

Answer 2

Drew Cain Author

Level 1

0 points

Feb 13, 2025 4:19 PM in response to Camelot

WOW! Thank you so much for both the absolutely effective method AND the explanation about how it works. I've never used INDIRECT before, it's the perfect solution.

Thanks a MILLION!

Reply