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!

=if(isna(vlookup formula),"",vlookup formula)


"ExcelBee" wrote:

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!

That worked great. Thank you for the quick posting.


--
victorcab
------------------------------------------------------------------------
victorcab's Profile: http://www.excelforum.com/member.php...o&userid=13675
View this thread: http://www.excelforum.com/showthread...hreadid=400252

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)),"",VLOO KUP(cell,table,col_no,false))


Gord Dibben Excel MVP

On Mon, 29 Aug 2005 13:48:06 -0700, "ExcelBee"
wrote:

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!

Hi Gord Dibben,
This is the formula i am getting error message if the cell 'J' is
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
nowfal


--
nowfal
------------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=400252

You just repeat your initial formula but wrap it in the ISNA() function
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))


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=400252

Try...

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

Hope this helps!

In article ,
nowfal wrote:

Hi Gord Dibben,
This is the formula i am getting error message if the cell 'J' is
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
nowfal

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


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

- Amar


--
amario
------------------------------------------------------------------------
amario's Profile: http://www.excelforum.com/member.php...o&userid=26858
View this thread: http://www.excelforum.com/showthread...hreadid=400252

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
nowfal


--
nowfal
------------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=400252

I tried your formula and didn't get any errors. What error are you
getting?


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=400252

Hi Cutter,
Thanks for you quick response, I will explain,my
workbook is made for foreign currency transaction. so, the conversion
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 i
have to some adjustment formula on M2. Like If J2 is empty M2 should
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
NOWFAL


--
nowfal
------------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=400252

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


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=400252