# VLOOKUP - Issues with using FALSE

#### mikemmb

##### Board Regular
Hi All,
I have tried desperately to figure this out myself and avoid asking .... but!

Problems with a VLOOKUP function.
I have two columns (F7:F500 & G7:G500) performing a lookup into another worksheet (CallSigns) for data also in adjacent columns.

If I put the following in the cells (and a value in A7) it works perfectly except when it does not find a match, then it gives me the nearest value which is no good. I need an exact match or a return of #N/A:
(F7) =IF(A7>0,(VLOOKUP(A7,CallSigns!\$A\$1:\$D\$1000,2)),"")
(G7) =IF(A7>0,(VLOOKUP(A7,CallSigns!\$A\$1:\$D\$1000,3)),"")

(F7) =IF(A7>0,(VLOOKUP(A7,CallSigns!\$A\$1:\$D\$1000,2,FALSE)),"")
(G7) =IF(A7>0,(VLOOKUP(A7,CallSigns!\$A\$1:\$D\$1000,3,FALSE)),"")
Now the return in (F7 etc) works perfectly (and gives "#N/A" if no value).
But the formula will not enter correctly into cell (G7), the cell displays the formula text no matter what I do. I have looked at formatting, including copying/dragging from other good cells but no result.

If I put the following:
(F7) =IF(A7>0,(VLOOKUP(A7,CallSigns!\$A\$1:\$D\$1000,2,FALSE)),"")
(G7) =IF(A7>0,(VLOOKUP(A7,CallSigns!\$A\$1:\$D\$1000,3)),"")
Now the return in (F7 etc) works perfectly (and gives #N/A if no value).
Now (G7 etc) works fine except when it does not find a match, then it gives me the nearest value.

Is the problem to do with using multiple formula with FALSE statements/results or am I up the wrong tree?

Thanks,
Mike

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### WinteE

##### Well-known Member
Try

VLOOKUP(A7,CallSigns!\$A\$1:\$D\$1000,2,FALSE)

or

(VLOOKUP(A7,CallSigns!\$A\$1:\$D\$1000,2,0))

(VLOOKUP(A7,CallSigns!\$A\$1:\$D\$1000,2,FALSE))

Erik

##### MrExcel MVP
Is CallSigns!\$A\$1:\$D\$1000 sorted in ascending order on column A?

BTW, select the cells displaying a formula instead of a result, and do an Edit|Replace with Find What set to: = and Replace with to: =

#### mikemmb

##### Board Regular
Hi Guys,
Thanks for the replies, but will not get back onto it till Mon/Tue.

The data in column A of CallSigns is usually in ascending order (with gaps but no duplicates) but this is not absolute ........ so the answer had better be no!

I am not sure what you mean by the following?
BTW, select the cells displaying a formula instead of a result, and do an Edit|Replace with Find What set to: = and Replace with to: =
Can you explain please ....... to a newby!

Thanks,
Mike

##### MrExcel MVP
Hi Guys,
Thanks for the replies, but will not get back onto it till Mon/Tue.

The data in column A of CallSigns is usually in ascending order (with gaps but no duplicates) but this is not absolute ........ so the answer had better be no!

I am not sure what you mean by the following?
BTW, select the cells displaying a formula instead of a result, and do an Edit|Replace with Find What set to: = and Replace with to: =
Can you explain please ....... to a newby!

Thanks,
Mike

You said:

But the formula will not enter correctly into cell (G7), the cell displays the formula text no matter what I do. I have looked at formatting, including copying/dragging from other good cells but no result.

Select G7.
Activate Edit|Replace.
Set Find What to: =
Set Replace With to: =
Activate the Replace button.

G7 will now show a result, not the formula it contains.

Now back to the lookup issue.

If ascending order sort holds...

=IF(A7>0,(VLOOKUP(A7,CallSigns!\$A\$1:\$D\$1000,2)),"")

would become:
Code:
``````=IF(A7>0,IF(LOOKUP(A7,CallSigns!\$A\$1:\$A\$1000)=A7,
LOOKUP(A7,CallSigns!\$A\$1:\$A\$1000,CallSigns!\$D\$1:\$D\$1000),""),"")``````

Otherwise:

=IF(A7>0,(VLOOKUP(A7,CallSigns!\$A\$1:\$D\$1000,2,FALSE)),"")

would become:

Code:
``````=IF(\$A7>0,
IF(ISNUMBER(MATCH(\$A7,CallSigns!\$A\$1:\$A\$1000,0)),
INDEX(CallSigns!B\$1:B\$1000,MATCH(\$A7,CallSigns!\$A\$1:\$A\$1000,0)),
""),
"")``````

which you can copy across.

#### mikemmb

##### Board Regular
Hi All,
Thanks for your help, cracked it.

I followed Aladin's formula with addition of "No Data" put into the target cell if no Match.

=IF(\$A7>0,IF(ISNUMBER(MATCH(\$A7,CallSigns!\$A\$1:\$A\$1000,0)),INDEX(CallSigns!B\$1:B\$1000,MATCH(\$A7,CallSigns!\$A\$1:\$A\$1000,0)),"No Data"),"")

Thanks again,
Mike

Replies
15
Views
552
Replies
2
Views
596
Replies
15
Views
555
Replies
25
Views
1K
Replies
7
Views
530

1,190,770
Messages
5,982,830
Members
439,799
Latest member
matts12

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