Match Value and then offset
 

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

Match Value and then offset
 

Todd,

You could use a VLOOKUP

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

OR

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


HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=556508

Match Value and then offset
 

That did not work.


SteveG wrote:
Todd,

You could use a VLOOKUP

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

OR

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


HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=556508

Match Value and then offset
 

Todd,

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

In my example, column E represents column 1 in the array. If you want
to pull from column J (or the 6th column in the array or 5 columns to
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 then
there are probably trailing spaces after the word "Test". Delete
"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 use
this instead,

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

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


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

Commit with Ctrl-Shift-Enter not just enter.


HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=556508