Vlookup with multiple input and multiple output

[lollino]

Hello,

I have a problem that is often discussed but I didn't found any solution that fits to me, may I ask you for an help?

I have the following table:

REGION Category AUTHO NAME SCO comment F BP IP BP FUNCTION
WORLDWIDE SBS 1000000 Lorenzo Text Fire F BP 1 IP BP 1 Text
WORLDWIDE SBS 1000000 Andrea Text Water F BP 2 IP BP 2 Text
WORLDWIDE SBS 500000 Marco Text Earth F BP 3 IP BP 2 Text
EAME CPG 50000 Lorenzo Text Sky F BP 3 IP BP 3 Text
EAME CPG 20000 Giulio Text Wind F BP 4 IP BP 4 Text


as you see, there's more than once Lorenzo, F BP 3 and IP BP2 for exemple


I would let the user select the preferred input: it's a multiple choice based on three columns:
exemple:
Region Category Autho
WORLDWIDE SBS 1'000'000


I would show the multiple outputs which may occurs in the following table:
Name Comment Finace BP Proc BP
Lorenzo Fire F BP 1 IP BP 1
Andrea Water F BP 2 IP BP 2



I tried with the following function:
=VLOOKUP(A4&B4&C4;CHOOSE({1,2};A18:A200&B18:B200&C18:C200;D18:D200);2;0)

where row(4) is the input line
and 18:200 is the whole table dimension.

unfortunately I don't know how to apply the same formula on a new line but starting from the row (instead of 18) right after the result that have been founded.

I would ask you for an help on this or if you have other simpler ideas!

Thank you very much,

Lorenzo

[Richard Buttrey]

Hi and welcome to the forum.

This is one of those cases where sight of a workbook is worth a thousand words of description.
If you upload the workbook and manually add the results you expect to see in the layout you want then I'm sure we'll be able to assist further. Be sure to explain which ranges are original data and which are results and if it's not blindingly obvious how you arrive at the results.

[lollino]

Hello,

thank you for your welcome and your answer,

please find here the exemple, http://www.excelforum.com/attachment...1&d=1460637762

I hope it will be clearer.

Best Regards,
Lorenzo

[Richard Buttrey]

Hi,

I suggest a Pivot Table as being the most efficient and elegant way.

See attached.