Solved

Excel - vlookup with 2 lookup values

Posted on 2014-09-14
18
131 Views
Last Modified: 2014-10-07
Hi

Is it possible to have a vlookup that uses 2 look up values ?

Thanks

My code looks like this now :

Sub test()
    On Error Resume Next

    Dim Insert_Row As Long
    Dim Insert_Clm As Long
    
    Table1 = Worksheets("GeneralPostingSetup").Range("GeneralPostingSetup[Produktbogføringsgruppe]")     ' Opslag
    Table2 = Worksheets("GeneralPostingSetup").Range("GeneralPostingSetup[Virksomhedsbogføringsgruppe]")     ' Opslag
    
    Lookup_Table_C5_InvenItemGroup = Worksheets("C5_InvenItemGroup").Range("_C5_InvenItemGroup[[Gruppe]:[RecId]]")   '
    Lookup_Table_C5_CustGroup = Worksheets("C5_CustGroup").Range("_C5_CustGroup[[Debitorgrp]:[RecId]]")   '
    
    Insert_Row = Worksheets("GeneralPostingSetup").Range("GeneralPostingSetup[Salgskonto]").Row
    Insert_Clm = Worksheets("GeneralPostingSetup").Range("GeneralPostingSetup[Salgskonto]").Column
    
    For Each cl In Table2
        Lookup_Value_C5_CustGroup = Application.WorksheetFunction.VLookup(cl, Lookup_Table_C5_CustGroup, 3, False)       'Omsætning DebitorGruppe
        If cl <> "" Then
            Worksheets("GeneralPostingSetup").Cells(Insert_Row, Insert_Clm) = Lookup_Value_C5_CustGroup
        End If
        Insert_Row = Insert_Row + 1
    Next cl
    
    Insert_Row = Worksheets("GeneralPostingSetup").Range("GeneralPostingSetup[Salgskonto]").Row
    Insert_Clm = Worksheets("GeneralPostingSetup").Range("GeneralPostingSetup[Salgskonto]").Column
    For Each cl In Table1
        Lookup_Value_C5_InvenItemGroup = Application.WorksheetFunction.VLookup(cl, Lookup_Table_C5_InvenItemGroup, 3, False)  'Omsætning Lagergruppe
        If cl <> "" Then
            Worksheets("GeneralPostingSetup").Cells(Insert_Row, Insert_Clm) = Lookup_Value_C5_InvenItemGroup
        End If
        Insert_Row = Insert_Row + 1
    Next cl

End Sub

Open in new window

0
Comment
Question by:conceptdata
  • 8
  • 7
  • 2
  • +1
18 Comments
 
LVL 12

Expert Comment

by:James Elliott
Comment Utility
You could concatenate the two fields.
0
 

Author Comment

by:conceptdata
Comment Utility
:) How ?

I'm not programming in vba normally :)


Thanks
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
yes. you can do it  like this.


=vloookup(lookupvalue&lookupvalue,lookuprange&lookuprange,columntoreturn,False)
0
 

Author Comment

by:conceptdata
Comment Utility
Is it possible to put it into my existing code, in a "for each"  ?
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
@ProfJimJam - I suspect it is just a typo but your suggestion has two lookuprange entries. VLOOKUP only works on one range.

@conceptdata - What format of result are you trying to extract from the lookuprange? If it is numeric, you may be to use a SUMIFS or SUMPRODUCT formula instead without the need for concatenation.

Thanks
Rob H
0
 

Author Comment

by:conceptdata
Comment Utility
-> Rob H.

Formats are Text.

Is it possible to use INDEX MATCH
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
An INDEX / MATCH combi does pretty much the same as VLOOKUP but with the advantage that it does not matter on the order of the columns; VLOOKUP looks in first/leftmost column of range and goes right, INDEX/MATCH can look in any column of the data range and return value from any direction.

Thanks
Rob H
0
 

Author Comment

by:conceptdata
Comment Utility
Is the another way to this in vba...

I have attached the excel File

The look up values in sheet "GeneralPostingSetup":
"Virksomhedsbogføringsgruppe"
"Produktbogføringsgruppe"

The look up ranges in sheet "C5_SystemAccounts" :
"Systemnavn"
"Gruppe"

If MATCH then Return Value from sheet "C5_SystemAccounts" - "Konto1"

THANKS......
0
 

Author Comment

by:conceptdata
Comment Utility
Attached FILE :)
TEST.xlsm
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
the solution to your question is attached.
Test.xlsm
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
just to mention that the UDF works as an array operations, when you insert the formula make sure to enter with CONTROL SHIFT ENTER .
0
 

Author Comment

by:conceptdata
Comment Utility
Hi ProfessorJimJam

That seems to Work fine, but I need only vba code, not any cell code.
It's because I have to combine it other lookups, and therefore i can not have cell code.

Sorry, maybe it was not clear in my question ....
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
The vba code is already provided to you in the workbook earlier attached. Open the workbook go to vba module and the see the code
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
here is your VBA UDF

Public Function MLOOKUP(GetFromRange As Range, ParamArray Condition() As Variant) As String

'  This function is create on 08-11-2007 ( August 11, 2007 )
'  Last updated on 27-09-2008 (September 27, 2008)
'  Source : Erik Winters, www.excelguide.eu
'  © all rights reserved


'  The use of this function is free on the next conditions :
'  - the source keeps stated;
'  - the code is not used to generate commercial profits;


'  For an explanation of this function you might visit www.excelguide.eu

Dim vItem As Variant
Dim rngCell As Variant
Dim lItem As Long
Dim lRange As Long
Dim varArray(99) As Variant
Dim lRngCount As Long
Dim lItemCount As Long
Dim x As Long
Dim y As Variant
Dim z As Long
Dim vCell As Variant

lItem = UBound(Condition()) + 1
z = 0

For Each vItem In Condition
    For Each rngCell In vItem
        lRange = lRange + 1
        If CBool(rngCell) = True Then
            y = 1
        Else
            y = 0
        End If
        sPrint = sPrint & y
    Next rngCell
    varArray(z) = sPrint
    sPrint = ""
    z = z + 1
Next vItem

lRange = (lRange / lItem) - 1
x = 1

For lRngCount = 0 To lRange
    For lItemCount = 0 To (lItem - 1)
        x = Mid(varArray(lItemCount), lRngCount + 1, 1) * x
    Next lItemCount
    If x = 1 Then
        MLOOKUP = GetFromRange.Rows(lRngCount + 1)
        Exit Function
    End If
    x = 1
Next lRngCount

End Function

Open in new window

0
 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
the reason I have sent that in your file was for your easy reference so that you know how to use it.

for more detail on this UDF and more example refer to
http://www.excelguide.eu/120/810/100/EN_MultipleLookUp.html
0
 

Author Comment

by:conceptdata
Comment Utility
Thanks - I will look at it Again tommorow.
0
 

Author Comment

by:conceptdata
Comment Utility
Hi again ProfessorJimJam

I have looked at the file you send me.

I see the vba code.

But, maybe i'm missing some thing:), the thing i need instead of the code
"=MLOOKUP('C5_SystemAccounts'!$G$3:$G$144;GeneralPostingSetup!A4='C5_SystemAccounts'!$C$4:$C$144;GeneralPostingSetup!B4='C5_SystemAccounts'!$F$4:$F$145)" in each cell, would be something like at foreach that rolls through the generalpostingsetup sheet, and calculates the c-column without "cell-code"


can you help me here - Thanks
0
 
LVL 25

Accepted Solution

by:
ProfessorJimJam earned 500 total points
Comment Utility
Hi conceptdata


here is the file with no formula and it just works with vba macro.   simply click on the button click me and it will do the work for you.
TEST.xlsm
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now