Need to lookup 2 columns in VBA Function

Question

I need to use to lookup columns in from another sheet and return in a single cell VBA. My Data in Sheet1 is,

P.No.  REV   Qty 
2918   01    50   
2918   02    44
2919   01    72

In Sheet2, It should return the Qty, by looking both the P.No. and REV columns. Kindly help me on this.

My code to lookup one column is below. In this I need to lookup two columns.

Function SingleCellExtractInward(lookupvalue As String, lookuprange As Range, ColumnNumber As Integer)
    Dim i As Double
    Dim Result1 As String
    Dim Result2 As String

    If Result2 = Empty Then
        Result2 = "no recent inward"
        SingleCellExtractInward = Result2
    End If

    For i = 1 To lookuprange.Columns(1).Cells.Count
        If lookuprange.Cells(i, 1) = lookupvalue Then
            Result1 = Result1 & " " & lookuprange.Cells(i, ColumnNumber) & ","
            SingleCellExtractInward = Left(Result1, Len(Result1) - 1)
        End If
    Next i
End Function

You can have a look here: [VLOOKUP on Two or More Criteria Columns](http://www.excel-university.com/vlookup-on-two-or-more-criteria-columns) or you need to show what you have already tried: Therefore [edit] your question add your VBA code and tell us where you got stuck. — Pᴇʜ, Jun 07 '17 at 12:47
Now you just posted a function. No one knows anything about it because you didn't tell us anything. Tell us what your code does, where it fails, what you expect it to do and so on (as detailed as possible). "*need to lookup two columns*" is just not enough information. Try to post a full working [mcve] and tell us which values the parameters of the function have. Remember we are here to help you to *solve your issue yourself* not to *solve the issue for you*. — Pᴇʜ, Jun 08 '17 at 06:10
By this code I can able to get the C column in Sheet1 to Sheet2 by the lookup reference Column A. It directly do the lookup function with the A (P.No.) column. Now I need to include the B column also as a input to get the C column in sheet2. It should lookup both A & B. I don't know how to use the for loop for this operation. — Divya, Jun 08 '17 at 06:25

score 0 · Answer 1 · answered Jun 07 '17 at 13:11

0

Try to use this function:

    Function LookupTwoCriteria(strPNo As String, strRev As String)

      LookupTwoCriteria = Evaluate("=INDEX(Sheet1!C:C,MATCH(1,(Sheet1!A:A=" & _
        strPNo & ")*(Sheet1!B:B=" & strRev & "),0))")

    End Function

It uses array formula that tries to match two criteria: strPNo & strRev. Use this in your cell like:

    =LookupTwoCriteria(A1, B1)

If it does not find a match, it will exhibit #VALUE! so guard it with:

    =IFERROR(LookupTwoCriteria(A4,B4),0)

if you desire so.

answered Jun 07 '17 at 13:11

Wils Mils

613
4
9

Thanks for your reply. If you can help me with my code I've added now, Kindly help me. – Divya Jun 08 '17 at 04:44
@Divya have you tried my code? I tested it using your example data and it works ok. I suggest that you try it first and then if you have questions how it works, we can answer them for you. – Wils Mils Jun 09 '17 at 01:24

Pᴇʜ · Accepted Answer · 2017-06-08T07:36:21.957

Therefore you just need to extend your function by a second lookupvalue, so we have lookupValueA and lookupValueB:
```
Function SingleCellExtractInward(lookupValueA As String, lookupValueB As String, _
                                 lookupRange As Range, ColumnNumber As Long)
```
Note that I changed Integer to Long. Always use Long instead of Integer unless you need to communicate with old APIs (see explanation here).
You need to check if both criteria are met:
```
If lookupRange.Cells(i, 1).Value = lookupValueA And _
   lookupRange.Cells(i, 2).Value = lookupValueB Then
```
Alternative: If you want to be able to additionally look only for one criteria A or B if the other one is empty then instead use:
```
If (lookupRange.Cells(i, 1).Value = lookupValueA Or lookupValueA = vbNullString) And _
   (lookupRange.Cells(i, 2).Value = lookupValueB Or lookupValueB = vbNullString) Then
```
In this case you can leave one of the lookupValue a vbNullString (same as "") and it only looks up one criteria (like your original function).

The following is just improvement and removing unnecessary code that I recommend:

i should be Long not Double:
```
Dim i As Long
```

If Result2 = Empty Then is always true at this point. So we don't even need Result2.
The following …

Dim Result2 As String

If Result2 = Empty Then
    Result2 = "no recent inward"
    SingleCellExtractInward = Result2
End If

Can be reduced to one line …

SingleCellExtractInward = "no recent inward"   'Default return value if nothing matches.

So we would end up with something like that:

Option Explicit

Function SingleCellExtractInward(lookupValueA As String, lookupValueB As String, _
                                 lookupRange As Range, ColumnNumber As Long)
    Dim i As Long
    Dim Result As String

    SingleCellExtractInward = "no recent inward"

    For i = 1 To lookupRange.Columns(1).Cells.Count
        If (lookupRange.Cells(i, 1).Value = lookupValueA Or lookupValueA = vbNullString) And _
           (lookupRange.Cells(i, 2).Value = lookupValueB Or lookupValueB = vbNullString) Then
            Result = Result & " " & lookupRange.Cells(i, ColumnNumber) & ","
            SingleCellExtractInward = Left(Result, Len(Result) - 1)
        End If
    Next i
End Function

Thanks for your reply. But the code returns only "no recent inward". It doesn't work with 2 lookup values. — Divya, Jun 08 '17 at 09:10
@Divya Please show how you call `SingleCellExtractInward` and add the expected output. I used `Debug.Print SingleCellExtractInward(2918, 1, Sheet1.Range("A1:C5"), 3)` in my test and it returned `50` (using the data table of your question). And please tell if `REV` values are formatted as number with leading zero or if formatted as text? — Pᴇʜ, Jun 08 '17 at 10:11
Hello Peh, Sorry for the late reply. REV values has to be in text format. I have tried the formula, =SingleCellExtractInward(B4,D4,'INWARDLIST'!$B$5:$F$26,5,FALSE) . It shows #Value error. if you find a solution, kindly help me. — Divya, Jun 19 '17 at 06:56
@Divya you have 5 parameters in your formula but my function only has 4 parameters. — Pᴇʜ, Jun 19 '17 at 07:28

Need to lookup 2 columns in VBA Function

2 Answers2