a somewhat complicated vlookup

[Guest]

Here is what I would like to do:
B J K
11 "Vacation" 11 "1616" 11 "Jones"
12 # on vac 12 "1615" 12 "Smith"
13 name 13 "Vac" 13 "Williams"
14 name 14 "vac" 14 "Doe"
15 name 15 "1605" 15 "Brown"
16 16
17 17
and on dowd to 35 to 40

the B column will stop at 17 but the J and K columns will gon on to the 35
or maybe 40th row indicating who has a car ex: 1616 or who is on vacation (
indicated only by the first three letters "vac".

My request is to somehow look up cell B11 ( in this case "vacation" other
cells will include "sick" "personal" and more) but concentrating on just the
B11 cell "vacation" I want to look for who is on "vacation" in the J and K
columns and then insert the name of the person on vac starting in B13 on down
to B17 ( I was close but what ended up happening was the formula returned
only one person on vac andf in all the cells from B13 down to b17.
Obviously, there will be more than one person on vac so how can I have it
enter a person one time and then look for the next person on vac and enter
their name next?
cell B12 will be designated for the total amount of people on vac.

I hope this was enough info, help is appreciated
Brian

 

[Frank Kabel]

Hi
enter the following array formula (entered with CTRL+SHIFT+ENTER) in
B13:
=INDEX($K$11:$K$35,SMALL(IF($J$11:$J$35="Vac",ROW($J$11:$J$35)),ROW(1:1
))-10)
and copy this down

in B12 enter
=COUNTIF(J11:J35,"vac")

 

[Guest]

I started to try your formula but I had to erase what I pasted and now I
can't because an error pops up stating that you cant change part of an array,
I cant delete the cell with the formula.
what can I do

 

[Frank Kabel]

Hi
first delete all cells conatining this formula. After this enter this
formula only in a single cell (you have entered this formula in
multiple cells at the same time)

 

[Guest]

I cant delete the cell it will not let me

Hi
first delete all cells conatining this formula. After this enter this
formula only in a single cell (you have entered this formula in
multiple cells at the same time)

 

[Guest]

The cell that i entered the formula in, is selected and for some reason
merged with the above cell. I cannot unselect the cell

 

[Frank Kabel]

Hi
send me your file

 

[Guest]

Thank You
I got it
one more question, is there a way to hide the #num! message in the cells
with a person on vac?

Thank you

 

[Frank Kabel]

Hi
you could change my INDEX(...) formula to
=IF(ISERROR(INDEX(...)),"",INDEX(...))
also an array formula. Just replace INDEX(...) <with the previous
formula

 

[Guest]

Sorry, I have almost no knowledge of excel and barely get by on what I do.
could you show me how your fix for #num! plugs into the original formula below

=INDEX($K$11:$K$35,SMALL(IF($J$11:$J$35="Vac",ROW($J$11:$J$35)),ROW(1:1
))-10)

you could change my INDEX(...) formula to
=IF(ISERROR(INDEX(...)),"",INDEX(...))
also an array formula. Just replace INDEX(...) <with the previous
formula

Thank you
Brian

 

[Frank Kabel]

Hi
as said
=IF(ISERROR(INDEX($K$11:$K$35,SMALL(IF($J$11:$J$35="Vac",ROW($J$11:$J$3
5)),ROW(1:1
))-10)),"",INDEX($K$11:$K$35,SMALL(IF($J$11:$J$35="Vac",ROW($J$11:$J$35
)),ROW(1:1
))-10))
as array formula