Excel Spreadsheet Query

  originalmiscellany 16:39 15 Jan 2006
Locked

Right:

I have a spreadsheet: Titles:
Column 1: Food
Column 2: Portion
Column 3: Calories

Column 1 is a validation list from a list of food types in alphabetical order, and Column 2 and 3 are vlookups (of column 1).

COlumn 1 consists of 10 rows, which are all drop down food types.

Now I want there to be an option to choose no food, and for column 2 and 3 to show 0 - at hte moment it shows #N/A rather than 0

So the formula I currently have is;
=VLOOKUP(A11,table,4)*B11

A11 is the food type,e..g lettuce
Table is the named cell of all the food types and calories,
4 is column 4 which contains the calories
and B11 tells us how many portions have been eaten.

Does that make sense?

Any help as ever would be appreciated.

Thanks

  VoG II 16:47 15 Jan 2006

=IF(ISERROR(VLOOKUP(A11,table,4)), 0, VLOOKUP(A11,table,4)*B11)

  originalmiscellany 19:15 15 Jan 2006

You're a good man!

I assume you are a man, and it's just the ticket.

  originalmiscellany 19:15 15 Jan 2006

You're a good man!

I assume you are a man, and it's just the ticket.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

iMac Pro review

Illustrator Charles Williams on how to create magazines and book covers

iMac Pro review

Les meilleures prises CPL (2018)