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

[Guest]

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

 

[Biff]

Hi!

Try this:

=VLOOKUP(LEFT(A1,3),C110,2,0)

Biff

 

[Guest]

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

 

[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