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

WPA2 hack: How secure is your Wi-Fi?

Add Depth Of Field to a photo using Tilt Shift Blur in Photoshop

iPhone tips & tricks

Les meilleures tablettes 2017