VLOOKUP on Matching Multiple Criteria

[msiyab]

Hi,

I would like to know a formula that will pull out the value from Sheet 1 to Sheet 2 (for example).

Sheet 1 has 5 Columns (Salesman, Territory, Dimension, Sales Amt, Cost). This sheet is basically a system export.

Sheet 2 has 5 Columns too (Salesman, Territory, Dimension, Sales Amt, Cost). This sheet is used for reports.


Could some one please guide me with a formula which will fetch result from Sheet 1 to Sheet 2 (Column "Sales"), only if the First 3 columns (Salesman, Territory, Dimension) match in both the sheets.

Example of Sheet 1 is as below:



Sales Man
Territory
Dimension
Sales Amt
Cost


John
New York
Tissue
1000
200


Alfred
Washington
Soda
2100
700


John
New York
Soda
2050
1500


Alfred
New York
Tissue
2000
500


Leo
Washington
Soda
200
100


Leo
New York
Tissue
3500
1500

[DocAElstein]

Please upload one or two workbooks, and show us what you want.

[msiyab]

Please upload one or two workbooks, and show us what you want.

I have attached a sample workbook. Sheet 1 "Export" is the system export report. Sheet 2 "Result" is the report template and how I want to get my result.

3444

[DocAElstein]

Hi
I think this might be possible to do with a formula.
But it is much too difficult for me to do. Sorry-

If nobody else at excelfox can help you, then possibly you could try at
mrexcel.com
or
excelforum.com ( https://www.excelforum.com/forumdisplay.php?f=8 )
or
eileenslounge.com


Good luck
Alan

(P.S. I could do a macro solution for you. I am better with VBA then with formulas )

[sandy666]

what the result table looks like before adding any values?

[sandy666]

is this ok?


Sales ManTerritoryDimension Sales Amt Cost

JohnNew YorkTissue
1000.00
200.00

AlfredWashingtonSoda
2100.00
700.00

JohnNew YorkSoda
2050.00
1500.00

AlfredNew YorkTissue
2000.00
500.00

LeoWashingtonSoda
200.00
100.00

LeoNew YorkTissue
3500.00
1500.00

MaxwellWashingtonTowel
1000.00
800.00



Result

Sales ManTerritoryDimensionSalesCost

MaxwellWashingtonTowel
1000
800

MaxwellWashingtonTissue

MaxwellWashingtonSoda

MaxwellWashingtonPaper

LeoNew YorkTissue
3500
1500

LeoWashingtonSoda
200
100

LeoWashingtonTissue

LeoWashingtonPaper

LeoWashingtonTowel

LeoNew YorkPaper

LeoNew YorkTowel

LeoNew YorkSoda

JohnNew YorkSoda
2050
1500

JohnNew YorkTissue
1000
200

JohnNew YorkTowel

JohnNew YorkPaper

JohnNew YorkTissue

JohnNew YorkPaper

JohnNew YorkSoda

JohnNew YorkTowel

AlfredWashingtonSoda
2100
700

AlfredNew YorkTissue
2000
500

AlfredNew YorkSoda

AlfredWashingtonTissue

AlfredNew YorkTowel

AlfredNew YorkPaper

AlfredWashingtonPaper

AlfredWashingtonTowel


Maxwell has one Territory so he has 4 records only

with 2 Territories for Maxwell result will be


Result

Sales ManTerritoryDimensionSalesCost

MaxwellWashingtonTowel
1000
800

MaxwellWashingtonTissue

MaxwellWashingtonPaper

MaxwellWashingtonSoda

MaxwellCoburgPaper

MaxwellCoburgTowel

MaxwellCoburgTissue

MaxwellCoburgSoda

LeoNew YorkTissue
3500
1500

LeoWashingtonSoda
200
100

LeoWashingtonTissue

LeoWashingtonTowel

LeoWashingtonPaper

LeoNew YorkTowel

LeoNew YorkPaper

LeoNew YorkSoda

JohnNew YorkSoda
2050
1500

JohnNew YorkTissue
1000
200

JohnNew YorkTissue

JohnNew YorkPaper

JohnNew YorkTowel

JohnNew YorkPaper

JohnNew YorkSoda

JohnNew YorkTowel

AlfredWashingtonSoda
2100
700

AlfredNew YorkTissue
2000
500

AlfredNew YorkTowel

AlfredWashingtonTissue

AlfredNew YorkSoda

AlfredNew YorkPaper

AlfredWashingtonPaper

AlfredWashingtonTowel


Solution (http://www.excelfox.com/forum/showthread.php/2672-Re-VLOOKUP-on-Matching-Multiple-Criteria?p=15040&viewfull=1#post15040)

[DocAElstein]

I took a quick look in the internet. A quick Google with a search of “VLOOKUP on Matching Multiple Criteria“ brings you a lot of infomation and examples…
To solve the problem with a single formula would be very difficult.
There are many similar problems solved on the internet which use a helper column.
A search of the internet will give you many examples. Here just a few
https://trumpexcel.com/vlookup-with-multiple-criteria/
https://exceljet.net/formula/vlookup-with-multiple-criteria

Example in uploaded file, VLookUpMultipleCriteria.xlsx
Your formula in row 2 to be copied down would be:
_____ Workbook: VLookUpMultipleCriteria.xlsx ( Using Excel 2007 32 bit )
Row\Col
D
E

2
=IF(ISERROR(VLOOKUP(A2&"|"&B2&"|"&C2,Export!$D$2:$F$8,2,FALSE)),"",VLOOKUP(A2&"|"&B2&"|"&C2,Export!$D$2:$F$8,2,FALSE))
=IF(ISERROR(VLOOKUP(A2&"|"&B2&"|"&C2,Export!$D$2:$F$8,3,FALSE)),"",VLOOKUP(A2&"|"&B2&"|"&C2,Export!$D$2:$F$8,3,FALSE))
Worksheet: Result



There is also some information on the internet to solve the problem without a helper column. But the solution does not use VLookUp.
It ends up to be a fucking long formula when applied to your data: ( It is a CSE formula! ) In row 2 to be copied down:
_____ Workbook: FuckingLongFormulaMultipleCriteria.xlsx ( Using Excel 2007 32 bit )
Row\Col
D
E

2
=IF(ISERROR(INDEX(Export!$E$2:$E$8,MATCH(1,(Result Index!A2=Export!$A$2:$A$8)*(ResultIndex!B2=Export! $B$2:$B$8)*(ResultIndex!C2=Export!$C$2:$C$8),0),1) ),"",INDEX(Export!$E$2:$E$8,MATCH(1,(ResultIndex!A2=Ex port!$A$2:$A$8)*(ResultIndex!B2=Export!$B$2:$B$8)* (ResultIndex!C2=Export!$C$2:$C$8),0),1))
=IF(ISERROR(INDEX(Export!$F$2:$F$8,MATCH(1,(Result Index!A2=Export!$A$2:$A$8)*(ResultIndex!B2=Export! $B$2:$B$8)*(ResultIndex!C2=Export!$C$2:$C$8),0),1) ),"",INDEX(Export!$F$2:$F$8,MATCH(1,(ResultIndex!A2=Ex port!$A$2:$A$8)*(ResultIndex!B2=Export!$B$2:$B$8)* (ResultIndex!C2=Export!$C$2:$C$8),0),1))
Worksheet: ResultIndex

Example in uploaded File, FuckingLongFormulaMultipleCriteria.xlsx

If you have a lot of real data, then such a fucking long formula is probably a bad idea.
A better solutions is probably Sandy666’s , or possibly a VBA solution


Alan

[sandy666]

A better solutions is probably Sandy666's , or possibly a VBA solution
so do that %D

[p45cal]

Another, in D2 of the Result sheet:

=IFERROR(INDEX(Export!D$1:D$8,MATCH($A2 & "¬" & $B2 & "¬" & $C2,Export!$A$1:$A$8 & "¬" & Export!$B$1:$B$8 & "¬" & Export!$C$1:$C$8,0)),"")
copy across and down.

The attached contains that formula, but as a bonus there is another offering at cell H1, being a Power Query table which just needs to be right-clicked and Refreshed, also see note in cell F9 of the Export sheet.

Edit post posting: AAghhh, I've just noticed https://www.excelfox.com/forum/showthread.php/2672-Re-VLOOKUP-on-Matching-Multiple-Criteria although we've done it differently.

[DocAElstein]

Hi P45cal
Its interesting to see some different ways to do this, ( and comparing ways is often more interesting than feedback, if any, from the OP, Lol ) I thought something like this had probably got different ways in Excel
I see your using IFERROR in the formula offering. I almost used that. But IFERROR was introduced at Excel 2007. I have a strange habit of liking to get stuff working first in earlier versions. Typically at about Excel 2003 is where I usually start.
If I understand properly its like this: IFERROR is pseudo
__( This formula if it doesn't error , This if that formula did error )
In our case like we want it to do nothing , that is to say, leaving the cell empty, " " , if the formula errors, as that happens if it found no match. So like
__( This formula if it doesn't error , " " )
I was using the 2003 equivalent, since the ISERROR is available and returns True or False depending on if a formula errors.
So I had like
_IF( ISERROR(The formula) , then " " , Otherwise The formula )

These would be the formulas I gave in IFERROR form. ( They still need to be CSE )
_____ Workbook: NotSoLongFormulasMultipleCriteria.xlsx ( Using Excel 2007 32 bit )
Row\Col
D
E

2
=IFERROR(INDEX(Export!$E$2:$E$8,MATCH(1,(ResultInd ex2!A2=Export!$A$2:$A$8)*(ResultIndex2!B2=Export!$ B$2:$B$8)*(ResultIndex2!C2=Export!$C$2:$C$8),0),1) ,"")
=IFERROR(INDEX(Export!$F$2:$F$8,MATCH(1,(ResultInd ex2!A2=Export!$A$2:$A$8)*(ResultIndex2!B2=Export!$ B$2:$B$8)*(ResultIndex2!C2=Export!$C$2:$C$8),0),1) ,"")
Worksheet: ResultIndex2

I put that in the uploaded file, NotSoLongFormulasMultipleCriteria.xlsx as well as your one. Also , for completeness I put the "IF(ISERROR , " " , … version of yours. That has this sort of form
_____ Workbook: NotSoLongFormulasMultipleCriteria.xlsx ( Using Excel 2007 32 bit )
Row\Col
D
E

2
=IF(ISERROR(INDEX(Export1!D$1:D$9,MATCH($A2 & "¬" & $B2 & "¬" & $C2,Export1!$A$1:$A$9 & "¬" & Export1!$B$1:$B$9 & "¬" & Export1!$C$1:$C$9,0))),"",INDEX(Export1!D$1:D$9,MATCH($A2 & "¬" & $B2 & "¬" & $C2,Export1!$A$1:$A$9 & "¬" & Export1!$B$1:$B$9 & "¬" & Export1!$C$1:$C$9,0)))
=IF(ISERROR(INDEX(Export1!E$1:E$9,MATCH($A2 & "¬" & $B2 & "¬" & $C2,Export1!$A$1:$A$9 & "¬" & Export1!$B$1:$B$9 & "¬" & Export1!$C$1:$C$9,0))),"",INDEX(Export1!E$1:E$9,MATCH($A2 & "¬" & $B2 & "¬" & $C2,Export1!$A$1:$A$9 & "¬" & Export1!$B$1:$B$9 & "¬" & Export1!$C$1:$C$9,0)))
Worksheet: P45cal




I expect sandy might be interested in your PQ offering. ( I still don't have a clue about any of that stuff… too much to learn in a lifetime, Lol.. maybe I will get around to learning PQ one day… )

There are probably infinite ways to do it in VBA. I will probably add one later


Alan

[sandy666]

I expect sandy might be interested in your PQ offering
this is almost the same as mine, so nothing new :)
http://www.excelfox.com/forum/showthread.php/2672-Re-VLOOKUP-on-Matching-Multiple-Criteria?p=15040&viewfull=1#post15040

[DocAElstein]

Here is one way in VBA, one of very many possibilities.
I am doing a VBA array solution, just because I am more comfortable with those things.

General coding Strategy
Rem 1 Make or get our arrays
The basic idea is to have a lot of 1 "column" or 1 "width" arrays for each worksheet, ( which I keep the same size "length" for each worksheet to help with cross referencing indicies )
I have these arrays:
_ SalesAmt1() Cost1() for data from the input ( Sheet1 / Export LookUp table, or whatever its referred to …. In the macro: ' CHANGE TO SUIT ).
_ Correspondingly SalesAmt2() Cost2() are for the output ( Result, Sheet 2 or whatever the output Worksheet is referred to …. In the macro : ' CHANGE TO SUIT ) , and gets filled by the macro
_ For both worksheets, I take in a 3 column array for columns Sales Man Territory Dimension , SMTD1() SMTD2() , which I then use to make an array of the concatenated values. SM_T_D1() SM_T_D2() ( Here is a schematic of what SM_T_D1() SM_T_D2() look like : https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=15015&viewfull=1#post15015 )

Rem 2 Build Output arrays
The main working of the macro then is to loop down the concatenated array from the output worksheet, SMTD1() and Application.Match it to the input data concatenated array, SMTD1()
Then I know where to find the matching Sales Amt and Cost data in SalesAmt1() Cost1() which I then put in SalesAmt2() Cost2() at the current loop position, ResRw


Here's the macro

Option Explicit
Sub Arrays1() ' https://excelfox.com/forum/showthread.php/2667-VLOOKUP-on-Matching-Multiple-Criteria
Rem 0 Worksheets info
Dim Ws1 As Worksheet, Ws2 As Worksheet
Set Ws1 = ThisWorkbook.Worksheets("Export1") ' CHANGE TO SUIT
Set Ws2 = ThisWorkbook.Worksheets("ResultVBA") ' CHANGE TO SUIT
Dim Lr1 As Long, Lr2 As Long ' For last row of data and for last row of Output range https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11466&viewfull=1#post11466
Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count).End(xlUp).Row: Lr2 = Ws2.Range("A" & Ws2.Rows.Count).End(xlUp).Row
Rem 1 Make or get our arrays
' get arrays
Dim SalesAmt1() As Variant, Cost1() As Variant ' for data from the input ( Sheet1 / Export LookUp table, or whatever its referred to …. In the macro: ' CHANGE TO SUIT ).
Dim SalesAmt2() As Variant, Cost2() As Variant ' for the output ( Result, Sheet 2 or whatever the output Worksheet is referred to …. In the macro : ' CHANGE TO SUIT ) , and gets filled by the macro
Dim SMTD1() As Variant, SMTD2() As Variant ' For both worksheets, I take in a 3 column array for columns Sales Man Territory Dimension which I then use to make a single column array of the concatenated values. SM_T_D1() SM_T_D2()
' ' I needs to have Dim arr() As Variant because: Variant type, because the next code lines .Value property returns a field of elements which are housed in elements of Variant type , so the type must be Variant regardles of what types are withing the elements or we will get type mismatch errors , and I need a dynamic ( not yet specified size ) array, as the working of the :Value property is such that it sends infomation to size the thing taking it: If we have a fixed size array as in then Excel will error becuse Excel will insist on wanting to do this sizing of any recieving array, which it can't do if the array is fixed size.
'1a) get some arrays
Let SalesAmt1() = Ws1.Range("D1:D" & Lr1 & "").Value: Cost1() = Ws1.Range("E1:E" & Lr1 & "").Value
Let SalesAmt2() = Ws2.Range("D1:D" & Lr2 & "").Value: Cost2() = Ws2.Range("E1:E" & Lr2 & "").Value ' This will conveniantly size our array and put the Header in
Let SMTD1() = Ws1.Range("A1:C" & Lr1 & "").Value ' The data to be searched in for a match
Let SMTD2() = Ws2.Range("A1:C" & Lr2 & "").Value ' The data to be searched in to find a match
'1b) make the two arrays for our concatenated values
Dim SM_T_D1() As String, SM_T_D2() As String ' For single column array of the concatenated values. I know the size, so does not neet to be dynamic but unfortunately I must make the array dynamic initially since Dim will only take numbers, and i want to give the "row" size from the known dimension of the previous arrays. The ReDim statement allows me givee a variable when sizing an array I will be filling these arrays in a loop, so i can choose whatever type I want
ReDim SM_T_D1(1 To Lr1): ReDim SM_T_D2(1 To Lr2)
Dim Cnt As Long ', arrRw() As Variant, ConcatStr As String
For Cnt = 1 To Lr1 ' '1b)(i) For the input data range
' Let arrRw() = Application.Index(SMTD1(), Cnt, 0) ' This picks the row out, https://usefulgyaan.wordpress.com/2013/06/12/vba-trick-of-the-week-slicing-an-array-without-loop-application-index/ , and it returns a 1D array : https://www.excelforum.com/showthread.php?t=758402&p=5408376#post5408376 '
' Let ConcatStr = Join(arrRw(), " ") ' This joins the elements of that row array making a single string from it
' Let SM_T_D1(Cnt) = ConcatStr
Let SM_T_D1(Cnt) = Join(Application.Index(SMTD1(), Cnt, 0), " ") ' Simplifying the above commented out 3 steps
Next Cnt
For Cnt = 1 To Lr2 ' '1b)(ii) For the output data range
Let SM_T_D2(Cnt) = Join(Application.Index(SMTD2(), Cnt, 0), " ")
Next Cnt
' Let Ws2.Range("G1:G8").Value = Application.Transpose(SM_T_D1()): Ws2.Range("N1:N33").Value = Application.Transpose(SM_T_D2()) ' Just For demo
Rem 2 Build Output arrays
Dim ResRw As Long
For ResRw = 2 To Lr2 ' looping down the rows of concatenated data to be Srched for in the input data for a match
Dim MtchRes As Variant ' This will be an integer of the matched position along the input data concatenated array, or a string vbError if it finds no match
Let MtchRes = Application.Match(SM_T_D2(ResRw), SM_T_D1(), 0) ' I am looking for the concatenated data of this outout data row ResRw , in the entire array of the concatenated input data SM_T_D1() , and the 0 tells Match to look for an exact match
If IsError(MtchRes) Then
' in the case of an error we had no match so we do nothing
Else
Let SalesAmt2(ResRw, 1) = SalesAmt1(MtchRes, 1)
Let Cost2(ResRw, 1) = Cost1(MtchRes, 1)
End If
Next ResRw
Rem 3 output results
Let Ws2.Range("D1:D" & Lr2 & "").Value = SalesAmt2()
Let Ws2.Range("E1:E" & Lr2 & "").Value = Cost2()
End Sub

The macro seems to work.
Here is the results:
Before https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=15045&viewfull=1#post15045

After https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=15047&viewfull=1#post15047

[msiyab]

Example in uploaded file, VLookUpMultipleCriteria.xlsx
Your formula in row 2 to be copied down would be:
_____ Workbook: VLookUpMultipleCriteria.xlsx ( Using Excel 2007 32 bit )
Row\Col
D
E

2
=IF(ISERROR(VLOOKUP(A2&"|"&B2&"|"&C2,Export!$D$2:$F$8,2,FALSE)),"",VLOOKUP(A2&"|"&B2&"|"&C2,Export!$D$2:$F$8,2,FALSE))
=IF(ISERROR(VLOOKUP(A2&"|"&B2&"|"&C2,Export!$D$2:$F$8,3,FALSE)),"",VLOOKUP(A2&"|"&B2&"|"&C2,Export!$D$2:$F$8,3,FALSE))
Worksheet: Result


Alan

I used the Vlookup formula you provided here and it worked well. Will try out in our reports during the month end and will provide a feedback then.

[DocAElstein]

I used the Vlookup formula you provided ....

OK
Make sure you are using the range with the Helper Column for that formula.



Here some extra notes to clarify all the formulas you have from me and P45cal

With Helper column : https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=15049&viewfull=1#post15049

Without Helper column: https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=15050&viewfull=1#post15050


Alan

( P.S You have also had help from P45cal and sandy666 )

[msiyab]

Thanks to P45cal and sandy666 too for your help.