How do I return a blank for VLOOKUP instead of #N/A?

E

ExcelBee

I am doing a VLOOKUP from a large table against smaller table that is missing
some of the data to look-up against. I had a formula I used years ago that
used ERROR, but of course, cannot remember how it went. Help!
 
G

Gord Dibben

You don't need to use the ISERROR in a VLOOKUP formula.

That could mask another real error.

Best to use the ISNA Function

=IF(ISNA(VLOOKUP,cell,table,col_no,false)),"",VLOOKUP(cell,table,col_no,false))


Gord Dibben Excel MVP
 
N

nowfal

Hi Gord Dibben,
This is the formula i am getting error message if the cell 'J' i
empty, and the formula is in 'L'
=VLOOKUP(J2,RATE!$A$1:C$20,IF(I2="P",2,3),FALSE)
So how to correct it.
thanks and regards
nowfa
 
C

Cutter

You just repeat your initial formula but wrap it in the ISNA() functio
and add the "" or whatever else you want Excel to show if the ISNA(
results in TRUE.

=IF(ISNA(VLOOKUP(J2,RATE!$A$1:C$20,IF(I2="P",2,3),FALSE)),"",VLOOKUP(J2,RATE!$A$1:C$20,IF(I2="P",2,3),FALSE)
 
D

Domenic

Try...

=IF(J2<>"",VLOOKUP(J2,RATE!$A$1:C$20,IF(I2="P",2,3),FALSE),"")

Hope this helps!
 
A

amario

=IF(VLOOKUP(A12,$P$12:$U$3323,5,FALSE)="Good","Good","BAD")


How can I get the ISNA in there.. while maintaining my IF output. I'
like the box to tell me if there was a match.. if not.. then tell m
its bad.. while blanking out the #n/A as i'm comparing 2 sets of dat
against each other. Thanks!

- Ama
 
N

nowfal

Hi,
that is fine working, but the next cell having a formula
=IF(I2="S",CEILING(K2*L2,0.05),IF(I2="P",ROUNDDOWN(K2*L2/0.05,0)*0.05,""))
is getting error.
Any solution?
thanks
nowfa
 
N

nowfal

Hi Cutter,
Thanks for you quick response, I will explain,m
workbook is made for foreign currency transaction. so, the conversio
on based on mainly 3 cells
I------- J ------ K --- L
--- M
pur/sale--currency---------quantity------rate--------amount

as u know i made a rate table in the other sheet to get in the cell L2
In the cell M2
a formula
=IF(I2="S",CEILING(K2*L2,0.05),IF(I2="P",ROUNDDOWN(K2*L2/0.05,0)*0.05,""))
is there. When J2 is empty #VALUE message is showing in M2.. So
have to some adjustment formula on M2. Like If J2 is empty M2 shoul
be blank. I didn't get the idea. It may be a 3rd If formula. So
Any suggestion from you or anybody will be highly appreciated.
Thanks and regards
by
NOWFA
 
C

Cutter

OK. I see. When J2 is empty it affects L2.

So to take care of that adjust your formula to either this:

=IF(J2="","",IF(I2="S",CEILING(K2*L2,0.05),IF(I2="P",ROUNDDOWN(K2*L2/0.05,0)*0.05,"")))

OR this

=IF(J2<>"",IF(I2="S",CEILING(K2*L2,0.05),IF(I2="P",ROUNDDOWN(K2*L2/0.05,0)*0.05,"")),"")

These assume you want M2 to be blank if J2 is blank
 
N

nowfal

Hi Cutter,
Thank you very much, the first one itself much enough .
I didn't test the second one.
Keep help others, that will benefit you with unknown way. May God bless
you.
thanks and regards
nowfal
 
N

nowfal

Hi Cutter,
The earlier topic was as i said worked fine, bu
suddenly one related problem came. What happened is the the particula
cell have the formula when copying down, i think it carries somethin
on it, and that created a #VALUE error on my SUMPRODUCT area. When
tried individually deleted the copied cell the error goes. That mea
evenif that cell is blank but contains something. So thinking of th
better way to be pure blank . Can I have add something on that formul
that makes the cell as early as blank. For further details givin
below the details of macro works to copy the cells to down.

Range("A2:AH10000").Select
Selection.Copy
Range("A3").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=True
_
IconFileName:=False
Range("C2:H2,J2:K2,N2,O2:Q2,T2:V2,Y2:AA2,AD2:AF2").ClearContents
ActiveWindow.ScrollColumn = 1
Range("D2").Select
Range("L2").Select
ActiveCell.FormulaR1C1 = _

"=IF(ISNA((VLOOKUP(RC[-2],RATE!R1C1:R20C[-9],IF(RC[-3]=""P"",2,3),FALSE))),"""",VLOOKUP(RC[-2],RATE!R1C1:R20C[-9],IF(RC[-3]=""P"",2,3),FALSE))"

please have a look and advice me a solution.
thanks with regards
nowfa
 
N

nowfal

Hi,
Another way is to find solution for , if S3,X3,AC3,AH3 is no dat
or blank, delete the cell by code.
select the cells if it is blank
selection clear contents
so please help me to write the additional code.
that will sort my problem.
thanks and regards
nowfa
 
Top