I have a need to compare a range of data against a string of data. If there is a match, I would like to get the text or contents of a cell next to it. Any ideas?

Example: Identify name of person with SS# 123-45-6789

Data Range

Cell contents should return "John"

  • Usually, the column with a unique identifier of a person (SS# in your case) appears first. If you had this, the problem would simplify to the straightforward vlookup command.
    – user79865
    Commented Oct 24, 2015 at 5:10

2 Answers 2


You can achieve that by using the following formulas:


=IFERROR(OFFSET(A1, MATCH(C1, B1:B3, 0) - 1, 0), "nothing found")

=IFERROR(FILTER(A2:A4, B2:B4 = C2), "nothing found")

=IFERROR(QUERY(A2:B4, "SELECT A WHERE B = '" & C2 & "'"), "nothing found")

=IFERROR(ARRAYFORMULA(VLOOKUP(C2, {B2:B4, A2:A4}, 2, 0)), "nothing found")


enter image description here


I've created an example file for you: Google Sheets: If Cell text is “Text” get adjacent cell text


Very similar to @Jacob's first offer but shorter mainly for relying on a system error message (#N/A) where a value is not found:


Can be copied up or down without need to adjust the arrays (but C2 would adjust).


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.