IF statement help please?

[opgriffiths]

Hi all,

I am having an issue with trying to write a formula for a delivery note I have made using Excel.

So I have a box with a 'pull down' menu (C10) that I can select the product being delivered. In the box next to it (D10), I have a box that tells the customer the ABV of that product.

The product that is selected on the 'pull down' menu comes from another extra sheet (called 'Product List') where all the products are listed in the 'A' column. In the 'B' column is the ABV of that product.

Now when this sheet started I could easily make D10 automatically populate by writing multiple 'IF' statements in that cell. That was ok when there were only about 10 products as I could individually list all the relevant cells that could possibly be selected and everything was ok. The problem is that now that I have about 50+ products and I used the same method for a while but it has now become a very messy and long formula that doesn't really remain practical past 20 odd products.

This is the formula I used in D10...... =@IF(C10='Product List'!A1,'Product List'!B1,IF(C10='Product List'!A2,'Product List'!B2,IF(C10='Product List'!A3,'Product List'!B3,IF(C10='Product List'!A4,'Product List'!B4, and so on.....

Is there a way this formula can be written in a much tidier and shorter way?


I would be extremely grateful if anyone was able to help me with this please? (assuming it is even possible)



Cheers,
Olly

 

[Fluff]

Hi & welcome to MrExcel.
How about

=VLOOKUP(C10,'Product List'!A$1:B$100,2,0)

 

[opgriffiths]

Hi & welcome to MrExcel.
How about

=VLOOKUP(C10,'Product List'!A$1:B$100,2,0)

Thank You so much that is perfect...... works a treat!

The only thing now is that it comes up with '#N/A' when there is no option selected. How do I use the above formula but make the box blank until I select something please?


Many thanks again,

Oliver

 

[Fluff]

How about

=IFNA(VLOOKUP(C10,'Product List'!A$1:B$100,2,0),"")

 

[opgriffiths]

How about

=IFNA(VLOOKUP(C10,'Product List'!A$1:B$100,2,0),"")

Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.