Doing a VLOOKUP and returning multiple entries

[duma87]

Hi, I've been working on a sheet and have run into a problem. See the attached example to follow along.

Basically, I want to do a VLOOKUP of the value of A9 in A2:A6, then return the corresponding value from column D. Your basic VLOOKUP, but the problem is that it will only return the first value. I want it to return all values pertaining to A9 (in this case, X -- so the returned values would be orange, pink, and red).

I'd like the returned values to come up in a vertical list starting at, say, A11.

Thanks in advance for the help.

[vandanavai]

Hello

See attached file. Formulas in green coloured cells. Hope this helps u

[modytrane]

look at the example attached.
modytrane

[duma87]

Hello

See attached file. Formulas in green coloured cells. Hope this helps u

Thanks. That appears to be what I want, but could you explain what this does (specifically the bolded part) and how I could change it if my range of data is on another sheet:

$B$9&ROW()-9

What does the "-9" do?

Thanks.

[martindwilson]

using row() or even column() is a useful method of incrementing something if you put =a1&1 in a cell and dragged down it would simply repeat the 1 so =a2&1, a3&1 and so on so using row returns the row number the formula is in and as it is dragged down it increments. but say you want to go up 1,2,3 and your formula is in row 60 you'd need to take away the 59 to return 1.
so when you see say starting in 21st row =a1&row()-20 that is = a1&1 then dragged down a2&row()-20 is a2&2

[duma87]

using row() or even column() is a useful method of incrementing something if you put =a1&1 in a cell and dragged down it would simply repeat the 1 so =a2&1, a3&1 and so on so using row returns the row number the formula is in and as it is dragged down it increments. but say you want to go up 1,2,3 and your formula is in row 60 you'd need to take away the 59 to return 1.
so when you see say starting in 21st row =a1&row()-20 that is = a1&1 then dragged down a2&row()-20 is a2&2

Thanks that helped a lot. It looks like it's solved! Thanks everyone!