Multiple VLOOKUPs & Data Validation

[Karthik-Excelsior]

Hi Excel gurus,

I've a data set as given on this trix https://docs.google.com/spreadsheet/ccc?key=0Ao7RzFobcTErdGdqd0JISnRTMnVuaFlNR29EeEM2LXc&pli=1#gid=0

To the right of the data set, I've a table which shows the output. I would like to fill this table with data based on some selections that I make from the two drop-downs provided for 'Category' & 'Number set' . And based on the selections made, I would like to populate the top 5 products by volume. If you look at the sheet in the link given above, you'll get more clarity. Also, I've given a sample output table right below the output table on the sheet.

Any help is much appreciated. Thanks!

Karthik

 

[wizz]

Hi,
I copied your file on my computer and I changed a little your file in order to find the solution.
I inserted a row below the first row and I inserted in the cells C2,D2,E2 the valueS 0, 1 and 2.
The formula in cell L3 (where value C should be, according to your example) is
=INDEX($A$3:$A$26;MATCH(LARGE(($B$3:$B$26=$H$3)*(OFFSET($C$3:$C$26;0;HLOOKUP($J$3;$C$1:$E$2;2;0)));1);$C$3:$C$26;0);0)

After you insert it, press CTRL+SHIFT+ENTER because it's an array formula.

For the next cells just change the red 1 into 2..3..4..etc

If I was not clear, tell me and I will change your posted file.
For the moment I didn't changed it because maybe someone else will find a better solution.

 

[Karthik-Excelsior]

Hi Wizz,

Thanks for your help. But I'm afraid, I din't follow your process. Can you send me your file with all the changes you've mentioned made in it, and email it to me at karthigeyans87@gmail.com?

Thanks again!