IF with VLOOKUP

**SimonPulse** · 01-12-2010, 07:54 AM

Hi guys

I'm using this formula:

=IF(Customers!B3="","",VLOOKUP(C2,Customers!A1:Customers!B84,2,FALSE))

What I want to happen is for the cell to be empty if VLOOKUP finds nothing. It does do this but the other cells beside it come back with #N/A results. Do you know how I can keep ther other cells as they are? I've attached a demo of it so far.

Thanks

Si

**teylyn** · 01-12-2010, 08:04 AM

Simon, it would really help if you could point out in which cell of the four sheets you'd like to apply the formula. So far, I haven't found it.

**teylyn** · 01-12-2010, 08:08 AM

Found it. At last.

can you explain what you want to achieve with this:

=IF(Customers!B3="" ??

What is the connection with the rest of the formula? Can you explain the reasoning/logic in plain words?

**SimonPulse** · 01-12-2010, 08:11 AM

What I want to happen is for the customers details to be transfered from the Custormers sheet to the Summary sheet once they have been selected from the drop down box on the Summary sheet.

Not all customers need to have the country cell filled in on their details, so if this is blank then I'd like the corresponding cell on the Summary sheet to be blank too.

Does that make any sense?

**teylyn** · 01-12-2010, 08:42 AM

Hi,

you cannot check a fixed cell in sheet Customers, because you won't know which row to look at.

Your columns in sheet Customers and your columns in sheet Summary do not have the same order. Swap Town and country on the Summary sheet, so the order is the same. Then you can use just one formula for all the Vlookups, and that is this one:

=IF(ISBLANK(VLOOKUP($C2,Customers!$A$1:$P$84,COLUMN()-2,FALSE)),"",VLOOKUP($C2,Customers!$A$1:$P$84,COLUMN()-2,FALSE))

Put this formula in cell C2 on the summary sheet and copy right to column N. Then select C2 to N2 and copy down.

You should adjust your range name for the data validation in column B to include only the cells that are actually populated. you may want to have a google for dynamic range names.

hth