vlookup a cell that contains the same first 3 letters of the ref.

B

Brian

Hello,

In cell b1, vlookup cell A1 (in this case cell a1 contains "vacation") in
the range c1:d10 and return the 2nd column. Except vacation is never spelled
out in the range because it is only the first 3 letters "vac".

Can someone help me with the syntax for this request?

Thank You
Brian
 
B

Brian

oops!
What happens when I use this formula that you provided in several cells,
because what is happening is that there may be more than one employee on
vacation and in the cells that have this formula only the first "vac" is
found and is returning only the one employee's name. How do I get it to
recognize that an employee on vacation was found and move on to the next
employee on vacation.

Sorry, I did not anticipate this problem
 
B

Biff

Hi!

Well, that's a little different!

Try this formula entered as an array - CTRL,SHIFT,ENTER:

=INDEX($D$1:$D$10,SMALL(IF(LEFT($A$1,3)=$C$1:$C$10,ROW($A$1:$A$10)),ROW(1:1)))

Copy down until you get #NUM! errors (meaning, no more matches have been
found)

Biff
 
Top