Look at another sheet if cell is blank

Occasional Visitor

I am working on creating an excel file with multiple sheets containing a large amount of data. 

 

My goal is to have 1 Row containing an item and the row beside it having a formula for returning the price of the item. 

 

The price can come from 3 areas each on a separate sheet (1-3). The first sheet, sheet one contains the recent price we have for the item. If this is given it will be the price used. If it is blank as we do not have that data it will then look to sheet 2 to see what our main competitor has the item priced at. If this is unavailable it will reference sheet 3 which contains the average market price of the item. If all 3 are unavailable I want it to return "Unavailable" 

 

How do I go about creating this formula? 

 

My overall goal is to get this in power BI but i am setting it up in excel first

1 Reply

@ddb300 

=IF(VLOOKUP(A2;sheet1!$A$2:$B$25;2;FALSE)<>"";VLOOKUP(A2;sheet1!$A$2:$B$25;2;FALSE);IF(VLOOKUP(A2;sheet2!$A$2:$B$25;2;FALSE)<>"";VLOOKUP(A2;sheet2!$A$2:$B$25;2;FALSE);IF(VLOOKUP(A2;sheet3!$A$2:$B$25;2;FALSE)<>"";VLOOKUP(A2;sheet3!$A$2:$B$25;2;FALSE);"Unavailable")))

 

In my example i have data in range A2:B25 in all 3 seperate sheets.

In the main sheet i enter above formula in B2 and copy formula down.