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!

  • 1
    Your file isn't accessible. Please change the share permission.
    – Daniele
    Nov 3, 2022 at 18:58
  • It is set to share with anyone who has a link, so that's weird. Here's an image of the sheet instead: IMAGE Does that work?
    – JRM
    Nov 3, 2022 at 19:15

1 Answer 1



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")
  • Thank you so so much! That worked!
    – JRM
    Nov 3, 2022 at 19:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.