VLOOKUP Not Giving Result For Merged Cells

[hrayani]

Hello Friends,

Vlookup formula is not giving the desired results when some cells in the range are merged.

For Example.

A B
PO # Contract #

2) 301675
3) 301684 21130697
4) 301687

Column B2:B4 are merged cells

=vlookup(c1,a2:b4,2,0)

Now when i enter the po # 301675 in cell C1 the result shows contract # 21130697
But for the rest two PO # the result is showing 0 whereas it should show same
contract # for all 3 PO.

Hope anyone can provide a solution

Regards,

Humayun

 

[steve the fish]

The best solution is to stop using merged cells. You couldnt use a straight vlookup for what you have there. The only cell with a value in column B is the topmost cell of the merged cells which is why one works but the others produce 0.

 

[hrayani]

yes u r right... but that's the last option....

is there any other friend who can look at it ??? or is it impossible for the vlookup to work for the merged cells ??

 

[steve the fish]

You couldnt use a straight vlookup for what you have there. You could use a UDF but it wouldnt always be correct. Get rid of the merged cells. They arent ever necessary in spreadsheets.

 

[steve the fish]

Heres a UDF. Bear in mind it wont always be displaying the correct results.

Function MERGEVLOOKUP(Lookup_Value As Range, Lookup_Table As Range, Column_Index_Number As Integer)

Dim myRow As Long, c As Range

If Column_Index_Number > Lookup_Table.Columns.Count Then
    MERGEVLOOKUP = CVErr(xlErrRef)
    Exit Function
End If

myRow = Application.Match(Lookup_Value.Value, Lookup_Table.Columns(1), 0)

If Not IsError(myRow) Then
    For Each c In Lookup_Table.Cells(1).Offset(myRow - 1, Column_Index_Number - 1).MergeArea
        If Len(c.Value) > 0 Then
            MERGEVLOOKUP = c.Value
            Exit For
        End If
    Next
Else
    MERGEVLOOKUP = CVErr(xlErrNA)
End If

End Function

 

[hrayani]

Thanks,

I will try it and let you know.