Using vlookup on merged rows

[dominion]

Dear Excel experts

I have a worksheet that has in the first column (A), the entry i want to search using vlookup, and in the second (B), the entry i want the formula to return.

However, I realise that the person whom made the spreadsheet sometimes have multiple entries in (B) for each entry in (A), e.g. cells A5:A8 is a merged cell with a single value, with corresponding values in cells B5, B6, B7, and B8. Using the vlookup formula returns me just the value in B5.

Is there anyway I can get the formula to give me the values of B6, B7 or B8 as well?

Thanks!

[azumi]

Hi there, maybe provide sample workbook make thing a lot clearer

[dominion]

Hi!

I did a screenshot below to illustrate this

The products in Column A may have one or more Qualities in Column B attached to it.

When I use vlookup, it only returns the first Quality, e.g. for Product 108, it will only return CGF. Is there anyway I make the formulareturn all the Qualities attached to product 108, or choose which ones it does?

Capture.PNG

[azumi]

Perhaps something like this? Using array formula (means you need to
press CTRL+SHIFT+ENTER button all together, not ENTER alone, then you copied down)

Pls see the file attach

[adhawan06]

or may be you can create a unique list to lookup. It's very interesting and nothing just with the help of Countif.

Here is the attached book for your reference.

I just have added two extra column (actually it's one assuming your second column will look like same as i have just filled out blank cell with their values) to function this formulas. Waiting to hear from you

Unique Vlookup.png

[dominion]

thanks a lot adha and azumi; have added reputation for your kind help.

Can anyone explain the formula with the index,small,lookup? I can't seem to figure out the logic. also, if I want to list the results across a row instead of down a single column, how would I do this?