VLOOKUP function exact match seems not working

weirax · Mar 9th 2018

Hi All,

I'm new in this forum - good day to everyone.
I need your help in a problem that I could not figure myself why it is not working.

I have 2 tabs involved in my problem:
CLEANUP = this tab contains a table with header (A,B columns) which is filtered by A column criterias, B column is empty
ACTION = this tab contains a table with header (A:H)

In CLEANUP tab, I want a VLOOKUP function in column B. Here is my code:

=============================================
Dim lLastRow2 As Long

With Sheets("CLEANUP")
lLastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("B2:B" & lLastRow2)
.FormulaR1C1 = "=VLOOKUP(RC[-1], ACTION!R2C1:R200C5,5,FALSE)"
.Value = .Value
End With

End With
=============================================

The problem is that for the last few results are incorrect (there are no such records in ACTION tab table as I look up, however, it lists the first ACTION entry row result).
I tried with 0 instead of FALSE aswell, without success.

Can you please help me what can be the reason the exact match is not working properly?

Thank you in advance!

chirayuw · Mar 9th 2018

sample file please

weirax · Mar 9th 2018

Here you are

Ingo_Ingo · Mar 9th 2018

Hi,

The VBA code work well.
VLOOKUP don't find ddd, fff and zzz and show error.

Change in your code this line:

.FormulaR1C1 = "=VLOOKUP(RC[-1], ACTION!R2C1:R200C5,5,FALSE)"

with this

.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1], ACTION!R2C1:R200C5,5,FALSE),"""")"

but it depends on the excel version.

weirax · Mar 12th 2018

Hello, thank you very much for your answer.
Maybe the problem is on my side.
It is still shows incorrect records.
For example, for me in my workbook, ffff and zzz shows "comment1" instead of "N/A".
I replaced the code to yours and the result is still the same.
Do you have any idea, what can be the problem?

weirax · Mar 12th 2018

[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Capture.JPG","data-attachmentid":1200801}[/ATTACH]

chirayuw · Mar 12th 2018

I think it might be to do with calculation mode. It might be set to manual rather than automatic. Try this

Also added filter for the data

Code

Sub lookup()


Dim lLastRow2 As Long


With Sheets("CLEANUP")
lLastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("B2:B" & lLastRow2)
.Formula = "=IFERROR(VLOOKUP(A2,ACTION!A:E,5,0),""N/A"")"
.Value = .Value
End With
End With


Calculate


On Error Resume Next
ActiveSheet.AutoFilterMode = False


ActiveSheet.Range("$A$1:$B$" & lLastRow2).AutoFilter Field:=2, Criteria1:="<>N/A"


End Sub

Display More

weirax · Mar 12th 2018

It should not be the problem as I clear all results at the end of the execution. So no previous lookup results can remain in the table.
I tried your above code and now the result is: [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 64"]RESULT[/TD]
[TD="width: 154"]LOOKUP[/TD]

[/tr]

[tr]

[td]

aaa

[/td]

[td]

comment1

[/td]

[/tr]

[tr]

[td]

bbb

[/td]

[td]

comment2

[/td]

[/tr]

[tr]

[td]

ccc

[/td]

[td][/td]

[/tr]

[tr]

[td]

ddd

[/td]

[td][/td]

[/tr]

[tr]

[td]

eee

[/td]

[td]

comment1

[/td]

[/tr]

[tr]

[td]

fff

[/td]

[td]

comment1

[/td]

[/tr]

[tr]

[td]

ggg

[/td]

[td]

comment1

[/td]

[/tr]

[tr]

[td]

hhh

[/td]

[td]

comment1

[/td]

[/tr]

[tr]

[td]

zzz

[/td]

[td]

comment1

[/td]

[/tr]

[/TABLE]

Which is not correct.

weirax · Mar 12th 2018

Hello,

I think I found the problem. The reason was the filtering.
I removed the filters, rerun the macro and only after then enabled the filter and now it works fine.
Thank you very much for your help!

VLOOKUP function exact match seems not working

Participate now!

Share