Vlookup giving #N/A

[matt_the_brum]

I'm using a formula

=VLOOKUP(C10,Labour!$A$3:$L$12,HLOOKUP(A10,Labour!$B$1:$L$2,2,FALSE),FALSE)

To find a value in a table from two drop down menus. If the are n
values in the drop down menus (ie C10 or A10) then the cell reads #N/A
This is messing up the rest of my spreadsheet so I need it to be
blank cell if A10 or C10 are blank or overwritten.

Any help gratefully appreciated

 

[Niek Otten]

=IF(ISNA(YourFormula),"",YourFormula)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|
| I'm using a formula
|
| =VLOOKUP(C10,Labour!$A$3:$L$12,HLOOKUP(A10,Labour!$B$1:$L$2,2,FALSE),FALSE)
|
| To find a value in a table from two drop down menus. If the are no
| values in the drop down menus (ie C10 or A10) then the cell reads #N/A.
| This is messing up the rest of my spreadsheet so I need it to be a
| blank cell if A10 or C10 are blank or overwritten.
|
| Any help gratefully appreciated.
|
|
| --
| matt_the_brum
| ------------------------------------------------------------------------
| matt_the_brum's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=5751
| View this thread: http://www.excelforum.com/showthread.php?threadid=567885
|

 

[vumian]

hi man,

this maybe help you

=IF(ISNA(VLOOKUP(...)),,VLOOKUP(....)

 

[matt_the_brum]

Thanks guys, thats working. Unfortunately its now upsetting somethin
else on the spreadsheet. I'll probably be back later with simila
questions

 

[matt_the_brum]

Right then, I've used

IF(ISNA(my functio),"",my function)

which is returning the reqired blank cell.

But when I use this blank cell as part of a formula in another cell
get an error.

eg.
A1 has =IF(ISNA(my function),"",my function)
B1 has a value entered into it
C1 has =SUM(A1:B1)

C1 returns a value if A1 is true. C1 returns an error if A1 return
the ""

 

[matt_the_brum]

Right then, I've used

IF(ISNA(my functio),"",my function)

which is returning the reqired blank cell.

But when I use this blank cell as part of a formula in another cell
get an error.

eg.
A1 has =IF(ISNA(my function),"",my function)
B1 has a value entered into it
C1 has =SUM(A1:B1)

C1 returns a value if A1 is true. C1 returns an error if A1 return
the ""

 

[matt_the_brum]

Right then, I've used

IF(ISNA(my functio),"",my function)

which is returning the reqired blank cell.

But when I use this blank cell as part of a formula in another cell I
get an error.

eg.
A1 has =IF(ISNA(my function),"",my function)
B1 has a value entered into it
C1 has =SUM(A1:B1)

C1 returns a value if A1 is true. C1 returns an error if A1 returns
the "".

 

[matt_the_brum]

Right then, I've used

IF(ISNA(my functio),"",my function)

which is returning the reqired blank cell.

But when I use this blank cell as part of a formula in another cell
get an error.

eg.
A1 has =IF(ISNA(my function),"",my function)
B1 has a value entered into it
C1 has =SUM(A1:B1)

C1 returns a value if A1 is true. C1 returns an error if A1 return
the ""

 

[matt_the_brum]

Right then, I've used

IF(ISNA(my functio),"",my function)

which is returning the reqired blank cell.

But when I use this blank cell as part of a formula in another cell I
get an error.

eg.
A1 has =IF(ISNA(my function),"",my function)
B1 has a value entered into it
C1 has =SUM(A1:B1)

C1 returns a value if A1 is true. C1 returns an error if A1 returns
the "".

 

[matt_the_brum]

Right then, I've used

IF(ISNA(my function),"",my function)

which is returning the reqired blank cell.

But when I use this blank cell as part of a formula in another cell
get an error.

eg.
A1 has =IF(ISNA(my function),"",my function)
B1 has a value entered into it
C1 has =SUM(A1:B1)

C1 returns a value if A1 is true. C1 returns an error if A1 return
the ""

 

[vumian]

hi,

you should use this fx pls
=if(isna(vlookup(...)),,vlookup(....))

Do not use quote, just zero, then you format it to -

 

[Kevin Vaughn]

The only way C1 would return an error is if either A1 or B1 contained that
error. Sum ignores text and "" (or even " ") I for one don't understand why
you are having the problem you describe.

 

[matt_the_brum]

Thanks.
Taking the "" out of my formula got rid of all the errors.