VLOOKUP with multiple values - creating a drop down list showing alternative choices

Hi,
Good Day.
I have joined now. I happy to get / provide guidance in MS Excel for improving my skills / my friends skills.

Now, I am coming to my problem.

I am a cost estimator for new products. My end product is having lot of parts. Everytime, when I am estimating cost for end product, I am searching price, currency, supplier from the database.

Sheet 1
Part No. Description UOM Norms Supplier Currency Price
P1 Desc1 EA 1
P2 Desc2 LB 2
P3 Desc3 EA 2
P4 Desc4 EA 2
P5 Desc5 EA 4


Part No. Description Supplier Currency Price
P5 Desc5 Supp1 USD 3.5
P2 Desc2 Supp1 USD 2.5
P3 Desc3 Supp1 USD 6.8
P1 Desc1 Supp2 EUR 1.4
P2 Desc2 Supp2 EUR 1.3
P3 Desc3 Supp2 EUR 3.2
P1 Desc1 Supp3 INR 400
P2 Desc2 Supp3 INR 300
P3 Desc3 Supp3 INR 500
P1 Desc1 Supp4 GBP 2.78
P2 Desc2 Supp4 GBP 4.23
P3 Desc3 Supp4 GBP 6.22
P4 Desc4 Supp4 GBP 6.22
P1 Desc1 Supp5 USD 8.1
P2 Desc2 Supp5 USD 7.4
P3 Desc3 Supp5 USD 3.8
P4 Desc4 Supp5 USD 2.6
P1 Desc1 Supp6 USD 4.2
P2 Desc2 Supp6 USD 1.6

Please find the attached file for more details.

I would like to have,

In sheet 1, Cell E2 E3 E4 E5 E6, Suppliers name in dropdown list (BASED ON DATAS AVAILABLE IN SHEET 2)

In Sheet 1, Cell F2 G2, F3 G3... relevant details as per supplier name in E2 E3...

I believe, it is possible in excel using functions. But, I failed when I tried with VLOOKUP.

Please help me.

Thanks & Regards,
Saravanan R
Mobile: +91 99400 16098
Skype: ram.saravana
Mail: [email protected]

Hi,

Thank you very much for your reply.

I would like to have results in SHEET 1 in cell E2, E3, E4, E5, E6

In sheet 1, Cell E2 E3 E4 E5 E6, Suppliers name in dropdown list (BASED ON DATAS AVAILABLE IN SHEET 2)

In Sheet 1, Cell F2 G2, F3 G3... relevant details as per supplier name in E2 E3...

Thanks & Regards,
Saravanan R
Mobile: +91 99400 16098
Skype: ram.saravana
Mail: [email protected]

Hi,

Thank you very much for your quick reply.

Need I to define names for each & every cell (where I want result?)E2, E3, E4, E5, E6?

Since, I have around 200 parts for my end product, it is very difficult to define name for each cell.

I would like to have F2 & G2, F3 & G3... results according to E2, E3.... ( I would like to have automatically according to E2, E3... - not in drop down list)

Thanks & Regards,
Saravanan R
Mobile: +91 99400 16098
Skype: ram.saravana
Mail: [email protected]

Hi,

Find the updated file with Index+Array ,
In case of any change in range use Ctrl+Shift+Enter in column F & G instead of Just enter as it a array function

Punnam