Fill blank cell with established values

[emmysou]

I'm sure the answer is somewhere on here, but I can't figure out what to call it :/

I've attached a spreadsheet with my example. I'd like for Excel to supply the missing values in column B, having already established what the value should be in the previous rows.

Thanks so much!
Maggie

[tigeravatar]

emmysou,

Welcome to the forum!

If you only have three values, as in your sample workbook, then you can do nested IF statements. In the sample workbook, Cell B2 would be:
=IF(A2="","",IF(A2="Apple","Red",IF(A2="Banana","Yellow",IF(A2="Mango","Orange","Unknown Fruit"))))

And then copy down.

However, if you have a lot of items to match, then you can make a table and perform a vlookup. Using your sample workbook, you could make a table in columns D:E (fruits in column D, their color in column E). Then in cell B2, the formula would be:
=IF(A2="","",IF(ISNUMBER(MATCH(A2,D:D,0)),VLOOKUP(A2,D:E,2,FALSE),"Unknown Fruit"))

And then copy down.

Attached is a modified version of your sample workbook. Sheet1 shows the first method and Sheet2 shows the second method.

Hope that helps,
~tigeravatar

[emmysou]

Awesome. That totally works! Thanks for including both options