vlookup help

[ehsas69]

I have following formula.when the result is false it appears #n/a.how can I change ther result if false to zero instead of #n/a.Rgds
=VLOOKUP(K6,$A$5:$H$200,2)

 

[Aladin Akyurek]

I have following formula.when the result is false it appears #n/a.how can I change ther result if false to zero instead of #n/a.Rgds
=VLOOKUP(K6,$A$5:$H$200,2)

What is in K6?

 

[ehsas69]

it is a cell where matching is checked.It is a text.

 

[howzat]

Here's the longwinded approach:

=IF(ISNA(VLOOKUP(K6,$A$5:$H$200,2)),0,VLOOKUP(K6,$A$5:$H$200,2))

 

[just_jon]

See Aladin's posting here -- http://www.mrexcel.com/board2/viewtopic.php?t=62102 -- re the dread N/A.

 

[Aladin Akyurek]

it is a cell where matching is checked.It is a text.

You use:

=VLOOKUP(K6,$A$5:$H$200,2)

which implies that A5:H200 is sorted in ascending order on A5:A200. You can get #N/A (a) if K6 is lexically earlier than the value in A5 or (b) K6 is empty or houses a formula-blank for which nothing exists in A5:H200.

If (b), then:

=IF(K6<>"",VLOOKUP(K6,$A$5:$H$200,2),0)

 

[ehsas69]

=IF(ISNA(VLOOKUP(K6,$A$5:$H$200,2)),0,VLOOKUP(K6,$A$5:$H$200,2))

thank for help.I am using above formula.The problem which I am facing is that for one user ID I am getting value of another user ID.For example for user ID2 I am getting the value of user ID4.That means both ID2 and ID4 has same value.Could anyone please help what can be the problem.

 

[Andrew Poulsom]

To get an exact match you need to add another argument to the VLOOKUP function. Try:

=IF(ISNA(VLOOKUP(K6,$A$5:$H$200,2,FALSE)),0,VLOOKUP(K6,$A$5:$H$200,2,FALSE))