ExcelBanter - Vlookup giving #N/A

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Vlookup giving #N/A (https://www.excelbanter.com/excel-worksheet-functions/103003-vlookup-giving-n.html)

matt_the_brum

Vlookup giving #N/A

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...fo&userid=5751
View this thread: http://www.excelforum.com/showthread...hreadid=567885

Niek Otten

Vlookup giving #N/A

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"matt_the_brum" wrote in message
news:matt_the_brum.2byuk5_1154611833.7638@excelfor um-nospam.com...
|
| 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...fo&userid=5751
| View this thread: http://www.excelforum.com/showthread...hreadid=567885
|

vumian

Vlookup giving #N/A

hi man,

this maybe help you

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

--
vumian
------------------------------------------------------------------------
vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494
View this thread: http://www.excelforum.com/showthread...hreadid=567885

matt_the_brum

Vlookup giving #N/A

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

--
matt_the_brum
------------------------------------------------------------------------
matt_the_brum's Profile: http://www.excelforum.com/member.php...fo&userid=5751
View this thread: http://www.excelforum.com/showthread...hreadid=567885

matt_the_brum

Vlookup giving #N/A

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
------------------------------------------------------------------------
matt_the_brum's Profile: http://www.excelforum.com/member.php...fo&userid=5751
View this thread: http://www.excelforum.com/showthread...hreadid=567885

matt_the_brum

Vlookup giving #N/A

matt_the_brum

Vlookup giving #N/A

matt_the_brum

Vlookup giving #N/A

matt_the_brum

Vlookup giving #N/A

matt_the_brum

Vlookup giving #N/A

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 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
------------------------------------------------------------------------
matt_the_brum's Profile: http://www.excelforum.com/member.php...fo&userid=5751
View this thread: http://www.excelforum.com/showthread...hreadid=567885

vumian

Vlookup giving #N/A

hi,

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

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

--
vumian
------------------------------------------------------------------------
vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494
View this thread: http://www.excelforum.com/showthread...hreadid=567885

Kevin Vaughn

Vlookup giving #N/A

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.
--
Kevin Vaughn

"matt_the_brum" wrote:

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 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
------------------------------------------------------------------------
matt_the_brum's Profile: http://www.excelforum.com/member.php...fo&userid=5751
View this thread: http://www.excelforum.com/showthread...hreadid=567885

matt_the_brum

Vlookup giving #N/A

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

--
matt_the_brum
------------------------------------------------------------------------
matt_the_brum's Profile: http://www.excelforum.com/member.php...fo&userid=5751
View this thread: http://www.excelforum.com/showthread...hreadid=567885

All times are GMT +1. The time now is 01:20 AM.