Match Value and then offset

T

Todd Huttenstine

Hey

I think this should be simple but I cant figure it out...

I have some values in a range (E1:E10). I want to find the value
"Test" in that range, and then once I find it, I want to offset 0 rows
and 5 columns over to the right and return the value of that cell.

How would I do this?


Thanks
Todd
 
S

SteveG

Todd,

You could use a VLOOKUP

=VLOOKUP("Test",E1:I10,5,FALSE)

OR

=INDEX(E1:I10,MATCH("Test",E1:E10,0),5)


HTH

Stev
 
S

SteveG

Todd,

In what way? Did you get the N/A error? Did it return the value fro
the wrong column?

In my example, column E represents column 1 in the array. If you wan
to pull from column J (or the 6th column in the array or 5 columns t
the right of E) try,

=VLOOKUP("Test",E1:J10,6,FALSE)

If it returned the N/A error and "Test" is in E1:E10 somewhere the
there are probably trailing spaces after the word "Test". Delet
"Test" and re-type being careful not to hit the spacebar at the end.
If the formula now returns a result, that was the issue. You could us
this instead,

=INDEX(E1:J10,MATCH("Test*",E1:E10,0),6)

The only issue with using the wildcard * is if you have Test 1 befor
Test in your range, it will return the data for Test 1. You could tr
this array formula instead.


=INDEX(E1:J10,MATCH("Test",TRIM(E1:E10),0),6)

Commit with Ctrl-Shift-Enter not just enter.


HTH

Stev
 
Top