# vlookup help

#### ehsas69

##### Board Regular
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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

##### MrExcel MVP
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?

#### ehsas69

##### Board Regular
it is a cell where matching is checked.It is a text.

#### howzat

##### Board Regular
Here's the longwinded approach:

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

##### MrExcel MVP
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
=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
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))

Replies
6
Views
144
Replies
1
Views
121
Replies
8
Views
188
Replies
3
Views
102
Replies
1
Views
128

1,186,924
Messages
5,960,584
Members
438,486
Latest member
ncc84330

### 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.

### Which adblocker are you using?

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

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