How to skip blank cells and use formula

**emp2013** · 07-16-2014, 07:24 PM

I am trying to figure out how to have my formula work even though there is a blank cell in some of rows on my table.

Right now I have a vlookup table that looks like this.

Reference	Output
Grape	Fruit
Orange	Fruit
Celery	Vegetable
Lettuce	Vegetable
Bread	Other
Eggs	Other

I am using this Vlookup table to run the output for the table below

Item 1	Item 2	Output
Grape	Celery	Fruit
Grape	Eggs	Fruit
Celery	Eggs	Vegetable
Orange		Fruit

The formula that is pulling these results is =IF(OR(VLOOKUP(G7,$A$3:$B$9,2,FALSE)="Fruit",VLOOKUP(H7,$A$3:$B$9,2,FALSE)="Fruit"),"Fruit",IF(OR(VLOOKUP(G7,$A$3:$B$9,2,FALSE)="Vegetable",VLOOKUP(H7,$A$3:$B$9,2,FALSE)="Vegetable"),"Vegetable","Other"))

My goal was for the output to identify items in an hierarchy with it firstly identifying fruits, then vegetables and everything else as other. However, in the case where the "Item" column is blank, the output results in #N/A.

What can I do for my formula so that it ignores the blank cell and runs the formula anyway. In this case, in row 4 for it to display "fruit" even though there is a blank item.

Any help with formulas or macros would be much appreciated!!

**humdingaling** · 07-16-2014, 08:56 PM

just put a vlookup around the whole formula

Formula:

Please Login or Register  to view this content.

also provide a worksheet next time
makes it easier for people to answer

also included an alternative way of righting the formula
which is based on the order of your reference table
fruit>vegetable>other

this will help you grow if you have more categories
fruit>vegetable>other>fish>meat>etc

as long as your table is sorted in the order you want for preferences