If you are doing a concatenation of lookup parameters so you can use VLOOKUP on what would otherwise be a multi-criteria lookup, you can use an array formula instead.
'Code goes in regular module sheet
Function LookupAlt(LookupTable As Range, ResultColumn As Long, CritCol1 As Long, Crit1, CritCol2 As Long, Crit2, _
CritCol3 As Long, Crit3, CritCol4 As Long, Crit4, CritCol5 As Long, Crit5, CritCol6 As Long, Crit6, _
CritCol7 As Long, Crit7)
Dim rg1 As Range, rg2 As Range, rg3 As Range, rg4 As Range, rg5 As Range, rg6 As Range, rg7 As Range, rgResults As Range
Set rg1 = LookupTable.Columns(CritCol1)
Set rg2 = LookupTable.Columns(CritCol2)
Set rg3 = LookupTable.Columns(CritCol3)
Set rg4 = LookupTable.Columns(CritCol4)
Set rg5 = LookupTable.Columns(CritCol5)
Set rg6 = LookupTable.Columns(CritCol6)
Set rg7 = LookupTable.Columns(CritCol7)
Set rgResults = LookupTable.Columns(ResultColumn)
LookupAlt = Application.Lookup(2, 1 / ((rg1 = Crit1) * (rg2 = Crit2) * (rg3 = Crit3) * (rg4 = Crit4) * (rg5 = Crit5) * (rg6 = Crit6) * (rg7 = Crit7)), rgResults)
End Function
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
Generating a graph via Excel | 3 | 25 | |
Troubleshooting a Worksheet Graphic | 8 | 25 | |
Looking for macro to do version saving of workbooks in Excel 2016. | 6 | 38 | |
Hiding column macro | 10 | 28 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
22 Experts available now in Live!