Reputation: 697

Google Sheets Function to get value of adjacent cell based on key

I need some help figuring out some Google Sheets function. I'm trying to look for the value of an adjacent cell based on a key. The problem is that the table has several rows and columns to search for. For example:

  |    A    |    B    |    C    |    D    |
1 | Alpha   | 5       | Bravo   | 10      |
2 | Charlie | 15      | Delta   | 20      |

The list goes on for several more rows and colums. What I'm looking for for example, is when a search use "Delta" as my search key, it will return the value 20 for me. I appreciate your help. Thanks!

Upvotes: 1

Answers (4)

Max Makhrov

Reputation: 18707

This could by done in several ways.

Use RegEx

=regexextract(CONCATENATE(A1:D),F6 & "(\d+)")

where F6 is cell with lookup value i.e. "Delta"

Use lookup

=INDEX(A:D,MATCH(LOOKUP(F6,A1:D,A1:A),A1:A),MATCH(F6,INDIRECT(MATCH(LOOKUP(F6,A1:D,A1:A),A1:A)&":"&MATCH(LOOKUP(F6,A1:D,A1:A),A1:A)))+1)

where F6 is cell with lookup value i.e. "Delta", look at example.

MATCH(LOOKUP(F6,A1:D,A1:A),A1:A) is used 3 times, so it could be counted in separate cell.

Upvotes: -1

franco

Reputation: 697

Here is the answer I got on the web application community:

https://webapps.stackexchange.com/questions/90198/how-to-get-value-of-adjacent-cell-based-on-key-if-the-table-is-split-into-multi

You can still use vlookup for this, by stacking the lookup tables into one using the array notation {first ; second; third} which means first above second above third. Example:
=vlookup("Delta", {A:B; C:D}, 2, False)
returns 20.

Upvotes: 3

Scott Craner

Reputation: 152505

You could use the following:

=ArrayFormula(INDEX($A$1:$D$2,MIN(IF($A$1:$D$2=$G$1,ROW($A$1:$D$2))),MIN(IF($A$1:$D$2=$G$1,COLUMN($A$1:$D$2)))+1))

Upvotes: -1

Ed Nelson

Reputation: 10259

Try:

=offset(lookup("Delta",A1:D2),0,1)

Upvotes: 0

Google Sheets Function to get value of adjacent cell based on key

Answers (4)

Related Questions