CODEWITHSUNDEEP
google-sheets
Josh Lawrenz
Josh Lawrenz

Reputation: 15

Looking for a value in a set range and returning a set value

I'm trying to search for a word in a range and then return the value that is two column to the right from where the search value is in the range.

I think the function is HLOOKUP, but I cant get it to work. I've also tried INDEX, MATCH, ARRAYFORMULA

=HLOOKUP("Foreman",'DB'!A2:2,2)

The searched value ("Foreman") could be in column E2, AA2, or and other cell in row 2. But I want to return the value that is two column to the right from where the searched value is in that row.

So if E2 = Foreman then return the value in G2 (2nd column to the right)

Foreman could be in any cell from column E to BB.

Upvotes: 1

Views: 28

Answers (1)

pnuts
pnuts

Reputation: 59485

Please try:

=index(2:2,2+match("foreman",2:2,0))

Upvotes: 0

Related Questions