VLOOKUP - Issues with using FALSE

[mikemmb]

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)),"")

So after reading the book/forums I added 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,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

 

[WinteE]

Try

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

or

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

instead of

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


Erik

 

[Aladin Akyurek]

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]

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

Aladin,
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

 

[Aladin Akyurek]

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

Aladin,
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:

=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:

=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]

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