In Google Sheets, how do I search for a specific value in a row, and return the exact value of the cell two columns to its left?

Question

I've created a sheet I can share with the public that re-creates my issue.

I need to input a formula in column O that searches columns B through J in the same row (formula in row 3, searches row 3, formula in row 4 searches row 4, etc) for cells that match "Mary" and I need it to output the value from 2 columns to the left of where it found "Mary."

So, in row 2, I want the output of the formula to be "IVAT," because we find "Mary" in column D and two columns to the left (in column B of the same row) we find "IVAT."

In row 3, the output would be "PSDOF". In row 4, "PSDOF". And so on.

I've tried HLOOKUP and MATCH and anything I can think of but I cannot, for the life of me, figure out how to do this seemingly simple formula.

Any and all help is greatly appreciated!

Answer 1

Welcome!

The first step - finding the cell with Mary was done exactly right: it is the MATCH() function that does the job. The function will return either a number (cell number from the beginning of the search range) or an #N/A error if the searched value is not in the range. Thus, if we subtract 2 from the resulting number, we will get an offset from the beginning of the range to the cell you need. And you can get the value from this cell using the INDEX() function. In case the required value is not in the range, wrap the entire formula in the IFERROR() function:

=IFERROR(INDEX(B2:J2,MATCH("Mary",B2:J2,0)-2),"Not found")