IF function with INDIRECT VLOOKUP?

**Environment** · 02-13-2012, 10:17 AM

Hi Everyone,

I hope someone can help me with what I think should be a very simple problem:

I’m trying to use the IF function in addition to VLOOKUP using the formula below:

=IF('Sheet1!$D2="","",(VLOOKUP(‘Sheet1!$D2,LIST,5,FALSE)))

I have used this formula before and it has worked, the only difference now is that the reference cell is in a different tab to the formula.

Should I be using some sort of INDIRECT or OFFSET function with this?

Thanks for any help you can provide.

E

**Domski** · 02-13-2012, 10:24 AM

The formula you posted should work fine so I'm not sure what your issue actually is. Could you post a sample workbook?

Dom

**daddylonglegs** · 02-13-2012, 10:27 AM

You have a single apostrophe before each sheet name - if the sheet name is exactly as shown you don't need those - try this

=IF(Sheet1!$D2="","",VLOOKUP(Sheet1!$D2,LIST,5,FALSE))

**Environment** · 02-13-2012, 10:43 AM

Hi,

I've tried making a simpl version of my spreadsheet to attached and to my surprise, my origional forumla worked!

So I'm stumped why it does not in my full version sheet.

The Error it comes up with is: #NAME?

Any ideas where I might be going wrong or what might be causing the conflict?

Thanks
E

**Domski** · 02-13-2012, 10:56 AM

Check that the reference of your defined name List is valid.

Dom

**Environment** · 02-13-2012, 11:27 AM

Thanks for your help everyone!!

The answer was I was being stupid!! My list reference was not to the first colomn within that list but the 3rd, confusing excel and me!!

Sorry to waste your time, but thanks again for all your help!

E