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.

Multiple Criteria.xlsx

[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 Man	Territory	Dimension	Sales Amt	Cost
John	New York	Tissue	1000.00	200.00
Alfred	Washington	Soda	2100.00	700.00
John	New York	Soda	2050.00	1500.00
Alfred	New York	Tissue	2000.00	500.00
Leo	Washington	Soda	200.00	100.00
Leo	New York	Tissue	3500.00	1500.00
Maxwell	Washington	Towel	1000.00	800.00
Result
Sales Man	Territory	Dimension	Sales	Cost
Maxwell	Washington	Towel	1000	800
Maxwell	Washington	Tissue
Maxwell	Washington	Soda
Maxwell	Washington	Paper
Leo	New York	Tissue	3500	1500
Leo	Washington	Soda	200	100
Leo	Washington	Tissue
Leo	Washington	Paper
Leo	Washington	Towel
Leo	New York	Paper
Leo	New York	Towel
Leo	New York	Soda
John	New York	Soda	2050	1500
John	New York	Tissue	1000	200
John	New York	Towel
John	New York	Paper
John	New York	Tissue
John	New York	Paper
John	New York	Soda
John	New York	Towel
Alfred	Washington	Soda	2100	700
Alfred	New York	Tissue	2000	500
Alfred	New York	Soda
Alfred	Washington	Tissue
Alfred	New York	Towel
Alfred	New York	Paper
Alfred	Washington	Paper
Alfred	Washington	Towel

Maxwell has one Territory so he has 4 records only

with 2 Territories for Maxwell result will be

Result
Sales Man	Territory	Dimension	Sales	Cost
Maxwell	Washington	Towel	1000	800
Maxwell	Washington	Tissue
Maxwell	Washington	Paper
Maxwell	Washington	Soda
Maxwell	Coburg	Paper
Maxwell	Coburg	Towel
Maxwell	Coburg	Tissue
Maxwell	Coburg	Soda
Leo	New York	Tissue	3500	1500
Leo	Washington	Soda	200	100
Leo	Washington	Tissue
Leo	Washington	Towel
Leo	Washington	Paper
Leo	New York	Towel
Leo	New York	Paper
Leo	New York	Soda
John	New York	Soda	2050	1500
John	New York	Tissue	1000	200
John	New York	Tissue
John	New York	Paper
John	New York	Towel
John	New York	Paper
John	New York	Soda
John	New York	Towel
Alfred	Washington	Soda	2100	700
Alfred	New York	Tissue	2000	500
Alfred	New York	Towel
Alfred	Washington	Tissue
Alfred	New York	Soda
Alfred	New York	Paper
Alfred	Washington	Paper
Alfred	Washington	Towel

Solution

[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...tiple-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,(ResultIndex!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=Export!$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,(ResultIndex!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=Export!$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

[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/showt...tiple-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,(ResultIndex2!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,(ResultIndex2!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