Vlookup - Im completley confused

[khalid79m]

Hi have been using vlookups for years but this one has stunned even me

Please look at row 4086 on sheet "Final" it contains the following formula in cell e4086


=IF(ISERROR(VLOOKUP($B4086,BCDUfeed!$A$2:$N$65536,12,FALSE)),"No Staff No BCDU",VLOOKUP($B4086,BCDUfeed!$A$2:$N$65536,12,FALSE))

the basically looks at b4086 and then searches sheet final ranged "a2:n65536" and returns value in column 12.

but it doesnt ? it returns the error message and the value in b4086 is definatley on sheet BCDUFeed at A500

any help ? this is a crucial part of a major project and i feel as though im going to fail , any help is greatly appreciated :help

[Aussiebear]

Are any of the values in Col E correct?

[xluser2007]

khalid,

I just had a quick look.

In the "final" worksheet some of the values are strings not numbers (even though they look like numbers).

For example in Final f4086 enter the formula "=B4086=BCDUfeed!A500". That is, do a logical check to see that the values exactly match (as they appear by glancing).

You get a FALSE i.e. they are not equal.

If you enter the formula "=B4086*1=BCDUfeed!A500" in another cell, you will get an exact match (TRUE)and vlookup will work on B4086*1 i.e. it is comparing a number with another equivalent number, not a number with a string (that looks very similar).

So check by multiplying by *1 each cell that you are comparing a similar formatted value.

HTH

[NY2DR]

Here is the solution:

In your "Final" tab, the number looks like "^43413637", and the format is as text.

In your "BCDUfeed" your number looks like this "43413637". is the format is also as text.

Remove or add the "^" and you will the results you need.


43413637
43413637

[Aussiebear]

Been thinking about this one a bit more and whilst the initial points regarding the values being stored as text rather than numbers may be valid, I'm not following the logic structure of the formula.


The formula reads

=IF(ISERROR(VLOOKUP($B4086,BDCUfeed!$A$2:$N$65536,12,False)),"No Staff No BCDU",VLOOKUP($B$4086,BCDUfeed!$A$2:$N$65536,12,False))


Since the Vlookups by themselves will return the value "A" the formula would then essentially be just



=IF(ISERROR(VLOOKUP("A")),"No Staff No BCDU",VLOOKUP("A")) Therefore if my thinking is correct, it will return "NO Staff No BCDU" if true and "A" if false.

Edited to correct a spelling mistake

[Aussiebear]

After playing around with this for a while longer, I find I was completely off track.

Initially I tried to format the values in Column A as Numbers, but this didn't work. Then I tried converting individual values to numbers and still it didn't work.... until I saved the workbook, then the values in column E returned the value "A".

Getting back to this formula.

=IF(ISERROR(VLOOKUP($B4086,BDCUfeed!$A$2:$N$65536,12,False)),"No Staff No BCDU",VLOOKUP($B$4086,BCDUfeed!$A$2:$N$65536,12,False))

If it cannot find the value (number) it wil return "No Staff No BCDU", should the value in column L be blank it will return a "0", otherwise it returns whatever the value is in column L of the BCDUfeed sheet where it finds the matching number from the Final sheet.

and that's where my mind is at.....:confused:

[Aussiebear]

As a further follow up, there is some code which places the values in column A of the BCDUfeed sheet as text rather than numbers, namely


Private Sub StaffNumberAmend2()
Sheets("BCDUfeed").Select
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
'Align staff numbers to 8 digits
With Range("K2:K" & Lastrow)
.NumberFormat = "0"
.FormulaR1C1 = "=IF(LEN(RC1)=7,TEXT(RC1,""00000000""),RC1)"
.NumberFormat = "@"
.Value = .Value
.NumberFormat = "0"
.HorizontalAlignment = xlLeft
.Cut
End With
Range("A2").Select
ActiveSheet.Paste
End Sub


So I can only assume that somewhere else in the code it will do the same to column B of the Feed sheet. Can this, .FormulaR1C1 = "=IF(LEN(RC1)=7,TEXT(RC1,""00000000""),RC1)", be changed to place numbers with 8 digits?

[tstav]

After running the following code in the attached by khalid79m file, all values in column A of Sheet 'BCDUfeed' were turned to text (they were not and of course the vlookup kept failing for all values).

Immediately after that, the vlookup of the 'Final' sheet seems to work just fine. Most values are found, others (very few) are not.


Sub TurnCellsToText()
Dim cel As Range
On Error GoTo ErrorHandler
Application.Calculation = xlCalculationManual
For Each cel In Range(Range("A2"), Range("A" & Rows.count).End(xlUp))
cel.Value = "'" & cel.Value
Next
ErrorHandler:
Application.Calculation = xlCalculationAutomatic
End Sub

[Aussiebear]

Does this still keep the 8 character format?

[tstav]

Does this still keep the 8 character format?
No it doesn't, becaude I hadn't realized it needed to be so (and apparently I hadn't read your prior comments carefully enough, Ted).

New code:

Sub TurnCellsToText()
'Turn cells from numeric to text and make then 8-char by adding leading zeros
Dim cel As Range
On Error GoTo ErrorHandler
Application.Calculation = xlCalculationManual
With Worksheets("BCDUfeed")
For Each cel In .Range(.Range("A2"), .Range("A" & .Rows.count).End(xlUp))
If Len(cel.Value) < 8 Then
cel.Value = String(8 - Len(cel.Value), "0") & cel.Value
Else
cel.Value = "'" & cel.Value
End If
Next
End With
ErrorHandler:
Application.Calculation = xlCalculationAutomatic
End Sub