vlookup help

ehsas69

Board Regular
Joined
Jan 10, 2004
Messages
221
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)
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
ehsas69 said:
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?
 

howzat

Board Regular
Joined
Oct 13, 2003
Messages
144
Here's the longwinded approach:

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
ehsas69 said:
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

Board Regular
Joined
Jan 10, 2004
Messages
221
=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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,096
Messages
5,768,068
Members
425,451
Latest member
JohnBrooksBiddle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top