Google-sheets – If Cell text is “Text” get adjacent cell text

google sheets

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

Cell contents should return "John"

Best Answer

You can achieve that by using the following formulas:

Formula

=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")

Screenshot

Example

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

Related Solutions

Google-sheets – How to use text from a cell as part of a formula

Please try:

 =indirect(E26&"!"&$AV$6)

Indirect is able to read a string as a cell reference. Within the parentheses three elements are concatenated: the content of E26 (which could be as here a sheet named 17.02.2014), the required "!" and the content of AV6 (anchored with $s so it does not change if the formula is copied around).

Google Sheets – Use Cell Value to Define Sheet Reference

You can do this with the indirect formula:

=ARRAYFORMULA(INDIRECT("'data("&A1&")'!A:A"))