Help with lookup using ISNA

[crimlet]

I need help with a lookup containing ISNA

could someone explain the syntax,

for a simple lookup e.g

=vlookup(target,lookup range,returned value,false)

for the above lookup i need a 0 returned if no value is found. I have tried looking at past posts but cant get it!

[Steve Jukes]

Why not try the iserror function, ie = if( iserror(then your lookup)=true,0,then your lookup)

Steve

[Aladin Akyurek]

On 2002-05-20 01:26, crimlet wrote:
I need help with a lookup containing ISNA

could someone explain the syntax,

for a simple lookup e.g

=vlookup(target,lookup range,returned value,false)

for the above lookup i need a 0 returned if no value is found. I have tried looking at past posts but cant get it!

See the figure.

Microsoft Excel - VLOOKUP.xls ___Running: xl2000 : OS = Windows (32-bit) NT 5.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
G24 =
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ID	Last Name	Salary
2	010008	:alert('=VLOOKUP(A2,MyLookup,2,FALSE)')>Smith	:alert('=VLOOKUP(A2,MyLookup,6)')>46,223.00
3	010002	:alert('=VLOOKUP(A3,MyLookup,2,FALSE)')>Miller	:alert('=VLOOKUP(A3,MyLookup,6)')>70,000.00		EXACT MATCH (0 or FALSE, both means the same thing to Excel)
4	010007	:alert('=VLOOKUP(A4,MyLookup,2,FALSE)')>Young	:alert('=VLOOKUP(A4,MyLookup,6)')>46,223.00		VLOOKUP(lookup-value,lookup-table,table-column-to-look-in,0)
5	010005	:alert('=VLOOKUP(A5,MyLookup,2,FALSE)')>Thomas	:alert('=VLOOKUP(A5,MyLookup,6)')>44,662.00				What is better?
6	010001	:alert('=VLOOKUP(A6,MyLookup,2,FALSE)')>Smith	:alert('=VLOOKUP(A6,MyLookup,6)')>70,000.00		Look Up	Salary?
7					010002	:alert('=VLOOKUP(E7,$A$2:$C$6,3,0)')>70,000.00	this
8						:alert('=IF(COUNTIF($A$2:$A$6,E7),VLOOKUP(E7,$A$2:$C$6,3,0),%22Not%20Found%22)')>70,000.00	this, if #N/A must be avoided, because efficient
9						:alert('=IF(ISNA(VLOOKUP(E7,$A$2:$C$6,4,0)),%22Not%20Found%22,VLOOKUP(E7,$A$2:$C$6,3,0))')>70,000.00	inefficient
10	Commission Table					:alert('=IF(ISERR(VLOOKUP(E7,$A$2:$C$6,3,0)),%22Not%20Found%22,VLOOKUP(E7,$A$2:$C$6,3,0))')>70,000.00	inefficient & bad practice
11	Sales	%				:alert('=IF(ISERROR(VLOOKUP(E7,$A$2:$C$6,3,0)),%22Not%20Found%22,VLOOKUP(E7,$A$2:$C$6,3,0))')>70,000.00	inefficient & bad practice
12	0	0.00%
13	2500	0.20%			APPROXIMATE MATCH (1 or TRUE or nothing, all means the same ting to Excel)
14	5000	0.40%			VLOOKUP(lookup-value,lookup-table,table-column-to-look-in)
15	6000	0.60%			Look Up	Commission
16	7000	0.80%			7,400.88	:alert('=VLOOKUP(E16,$A$12:$B$18,2)')>0.80%	this
17	8000	1.00%				:alert('=IF(ISNUMBER(MATCH(E16,$A$12:$A$18)),VLOOKUP(E16,$A$12:$B$18,2),%22Not%20Found%22)')>0.80%	this, if #N/A must be avoided, because efficient; seldom needed
18	9000	1.20%					As above, no ISNA, ISERR, or ISERROR.
19
20	SPECIAL NOTE: INDEX/MATCH is faster than VLOOKUP, in particular when the formula must be copied to a huge number of cells.
Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.26]
If you want FREE SOFT, click here and Colo will email the file to you
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


Addendum: ID's have number as uderlying format, but custom formatted as 000000,hence leading 0's. That's also the case in E7.

In case of failure, some of the formulas return "Not Found". This can be replaced by 0 or "".

Aladin

PS. I pulled off the Salary Table from the Internet .





[ This Message was edited by: Aladin Akyurek on 2002-05-20 02:12 ]

[ This Message was edited by: Aladin Akyurek on 2002-05-20 02:13 ]

[ This Message was edited by: Aladin Akyurek on 2002-05-20 03:30 ]

[ This Message was edited by: Aladin Akyurek on 2002-05-20 03:35 ]

[ This Message was edited by: Aladin Akyurek on 2002-05-20 05:43 ]