Excel Vlookup Formula Troubleshooting

Question

I'm goofing a bit on the syntax here.

But in most basic terms, if sheet 'CT', column B has a corresponding lookup value in column B of sheet 'CT WKSHT' AND column K of 'CT WKSHT' is N/A (this cell is a formula that will not always have a value) then I want to populate a 1 else a 0.

The formula below is sort of conceptually what I'm trying to do, but I'm not doing this right clearly.

Please help if you can, as I get stuck when I think about getting vlookups to have additional conditions on them.

=IFERROR(VLOOKUP(CT!B4,'CT WKSHT'!B8:K8,10,FALSE),1,"")

Answer 1

Why are you even working with VLookup()? If I read your formula, it reads like:

• Look for a value in column B and if you find it, return the value of column K.
  • If the lookup did not work, then show 1
  • If the lookup did work, then show and empty string

Why do you want to return value of column K if you overwrite it with an empty string anyway?

I would advise you the following formula:

=IFERROR(VLOOKUP(CT!B4,'CT WKSHT'!B8:K8,10,FALSE),1)

Is this better?

Also, you are looking for that value just on row 8, why not for the whole range:

=IFERROR(VLOOKUP(CT!B4,'CT WKSHT'!B:K,10,FALSE),1

Answer 2

This should do it. It combines a normal IF statement with ISERROR. ISERROR simply returns True or False depending on the result of the VLOOKUP.

=IF(ISERROR(VLOOKUP(DCTM!B4,'DCTM WKSHT'!B:K,10,FALSE)),1,0)