• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

LookUp using Multiple Condition

Debraj

Debraj

Excel Ninja
Hi Excelopedians,


Can someone please help me to create a drag-able FORMULA to get Margin.. via multiple Condition..


* Each Vendor has only one type of MARGIN decider (MRP or BRICK or TAX Code)

* According to Margin decider, I need to get MARGIN for the VENDOR..


i.e Vendor 1 (Chandoo) 's Margin Decider is BRICK..

If brick is ACC then Margin is 548, if Brick is JEW then Margin is 786.. (Check Table2 for lookupArea and Margin Decider..

[pre]
Code: 
VENDOR		BRICK		MRP		TAX CODE	Margin
=======================================================================
Chandoo		ACC		-		-
Hui		-		4284		-
Shri		APPR		-		-
Narayan		-		-		V12.5
Narayan		-		-		V12.5
Hui		-		4447		-
Chandoo		JEW		-		-
Hui		-		4266		-
Hui		-		2307		-
Shri		APPR		-		-
Shri		FOOTWEAR	-		-
Hui		-		1382		-
Hui		-		1504		-
Narayan		-		-		V0
Hui		-		3574		-
Shri		FOOTWEAR	-		-
Shri		FOOTWEAR	-		-
Chandoo		ACC		-		-
Narayan		-		-		V12.5
Narayan		-		-		V5
Shri		APPR		-		-
Narayan		-		-		V0

LookUpArea..

VENDOR		BRICK		MRP		TAX CODE	MARGIN
=======================================================================
Chandoo		ACC						548
Chandoo		JEW						786
Narayan						V12.5		637
Narayan						V5		875
Narayan						V0		528
Hui				>3000				694
Hui				<3000				519
Shri		APR						550
Shri		FOOTWEAR					529
[/pre]
* suggestion for changing Design of LookUpArea's is appreciable.

* use of HELPER column is also acceptable..

* if fetching Margin according to MRP is lil bit tricky, then you can overlook that part also..


https://dl.dropbox.com/u/78831150/Excel/MultipleLookup.xlsx


Regards,

=DEC2HEX(3563)
 
@Dec2Hex(3563)


Interesting problem.


I could get the values for everything except MRP lookups. Here is the formula.


=SUMIFS($O$3:$O$11, $K$3:$K$11,A2, INDEX($L$3:$N$11,,MATCH(E2,$L$2:$N$2,0)), INDEX(B2:D2,MATCH(E2,$B$1:$D$1,0)))


How it works?


1. We are going to SUM up the margin codes

2. Such that, name is given name

3. Margin Decider column matches Margin decider value

4. This is done by using INDEX to dynamically fetch a column from lookup table to match the margin decider value.


Assumption: Margin will always be number.


Hope that helps.


I would be curious to see if MRP lookups can also be done with same method.
 
@ chandoo.


Thanks for your response..


I am still working on DGET.. or D(function).. for MRP related issue..


Regards,

Deb
 
Hi Deb,


There's a typo in your LOOKUP table against my name. APR should be APPR to give right results. Here's my formula in E2 [Control + Shift + Enter as array formula] and copy down:


Code: 
=INDEX($K$2:$K$10,MATCH(SUBSTITUTE(A2&B2&IF(ISNUMBER(C2),IF(C2<3000,"<3000",">3000"),C2)&D2,"-",""),$G$2:$G$10&$H$2:$H$10&$I$2:$I$10&$J$2:$J$10,0))


Since the MRP lookup is string, I took liberty of inserting IF. But this is very case specific.
 
Hi Shri,


Thanks for response..

Sorry to say.. in the 1st table.."-" are not exactly "-".. they are just for "Overlook" purpose..

* Vendor's are agree to set their MARGIN according their criteria. Some vendor want to categories according to Brick, and some vendor according to Margin..


So concatenate will not work.. I need to check MARGIN DECIDER for each vendor..

i.e SHRI's Margin decider is Brick and Narayan's TAX CODE..


I am plannig to use DGET..

=DGET(LookUpArea,"MARGIN",$P$2:$Q$3)


after Press F9 for $P$2:$Q$3 it gives me {"Vendor","Brick";"Chandoo","JEW"}


But if I set Criteria Array dynamic.. {"Vendor",E2;A2,Choose(Match,...)} it fails.. Is there any option to set ARRAY by Formula..


Regards,

Deb
 
In your MultipleLookup sheet posted above, put following ARRAY formula in Cell E2:


=INDEX($N$3:$N$11,MATCH(SUBSTITUTE(A2&B2&IF(ISNUMBER(C2),IF(C2<3000,"<3000",">3000"),C2)&D2,"Need to Overlook",""),$J$3:$J$11&$K$3:$K$11&$L$3:$L$11&$M$3:$M$11,0))


and tell us if it works or doesn't.
 
Hi Deb,


Reading & re-reading this post is getting me confused. Please check this uploaded

file.


https://www.box.com/s/m4aqaqj08vlvh32cl4om


I have written comments along with formula.


Hth,
 
Hi Shri,


Thanks again, for giving your Time..


If you are looking at the 2nd sheet.. that fully testing page..

actual problem is provided in sheet 1..


I tried to decide MARGIN Decider for each vendor.. then according to Vendor.. & Margin decider.. I tried to MATCH & CONCAT or SUMIF (by chandoo) to decide correct MARGIN..


YES.. APR is typo.. and "Need to Overlook" can be anything.. if vendor's Margin Decider is BRICK, then other two Margin decider need to ignore..


If you want to follow concat method then.. Concat should be..

Code: 
=$A2&"INDEX($B2:$D2,MATCH(E2,$B$1:$D$1,0))

which is..

Chandoo|ACC

Hui|3555

Shri|APPR

Narayan|V12.5

Narayan|V5


and same need to lookup from lookup area..


Your formula working great.. if Margin Decider cell is filled and other are filled with some constant..

but fail if other cell are veriable.. which need to ignore..


Regards,

Deb
 
Hi Deb,


How is margin decider worked out in a normal case?


1. I suppose the combinations in Lookup_Area table are the ones that tell us the valid combination for margin.


2. Once this combination is found out then its margin rate shall be picked up!


Is this correct understanding? If yes, then we can try to work out a solution which will ignore inconsistent data in other two columns.
 
Yep.. you are in right Direction.. :)


1st table is ITEM WISE.. where LookUpTable is BRAND WISE..

For each brand MARGIN is decided according to vendor's Criteria.. MRP/Brick/Tax Code..

But we create Purchase Order according to ITEM..

I can Import CSV files for each vendor.. OneByOne..

but at the time of Reco.. I am planning to update total ItemMaster.. all at once... (just for Verify purpose)

so that.. I can get a better look for all the Reconsilation for all vendor.. in a single TABLE..


Yes.. you are correct.. I work for a E-commerce Site (Jabong.com).. :)


Regards,

Deb
 
Right-o then, In your "Sheet1" cell E2:

=INDEX($N$3:$N$11,SUMPRODUCT(--($J$3:$J$11=$A2),--(($K$3:$K$11=$B2)+($L$3:$L$11=$C2)+($M$3:$M$11=$D2)),--ROW($N$3:$N$11))-2)


It will error out on MRP and non-matching items. MRP is little difficult to deal with as MRP is listed in actual values and in lookup table it is string. Are MRPs lots of combinations or only two case that you have specified i.e. >3000 and <3000. I guess, that is not the case.


By the way, I have a curious question for you: is it ja'BONG' in reality as you work for 'em?
 
Hi Shri,


Thanks again and lot for the help..

It works.. except MRP condition..

I hope.. checking MRP and read sign ">" and then convert the text to Condition.. can be handle by D-Formulas..(which can also able to handle AND OR and multiple CRITERIA)

But again I stumped as unable to set the ARRAY area dynamically..


Do you have idea for this..

I am plannig to use DGET..

=DGET(LookUpArea,"MARGIN",$P$2:$Q$3)


after Press F9 for $P$2:$Q$3 it gives me {"Vendor","Brick";"Chandoo","JEW"}


But if I set Criteria Array dynamic.. {"Vendor",E2;A2,Choose(Match,...)} it fails.. Is there any option to set ARRAY by Formula..
Click to expand...

Regards,

Deb
 
A B C D E

VENDOR BRICK MRP TAX CODE Margin

Chandoo ACC 548

Hui 4284 519

Shri APPR 550

Narayan V12.5 637

Narayan V12.5 637

Hui 4447 519

Chandoo JEW 786

Hui 4266 519

Hui 2307 694

Shri APPR 550

Shri FOOTWEAR 529

Hui 1382 694

Hui 1504 694

Narayan V0 528

Hui 3574 519

Shri FOOTWEAR 529

Shri FOOTWEAR 529

Narayan V12.5 637

Narayan V5 875

Shri APPR 550

Narayan V0 528


K L M N O P

2 VENDOR| BRICK| MRP Start| MRP End TAX CODE MARGIN

3 Chandoo ACC 548

4 Chandoo JEW 786

5 Narayan V12.5 637

6 Narayan V5 875

7 Narayan V0 528

8 Hui sdfsd 0 3000 694

9 Hui dsaf 3001 5000 519

10 Shri APPR 550

11 Shri FOOTWEAR 529


Try this if it works... only one change that I have made here is for MRP I took Start range and End Range in a different columns. As per my understanding I've created this formula to get the values...


=IF(ISBLANK(B3)=FALSE,VLOOKUP(B3,$L$3:$P$11,5,0),IF(ISBLANK(C3)=FALSE,INDEX($P$3:$P$11,SUMPRODUCT(--($M$3:$M$11<=C3)*($N$3:$N$11>=C3),ROW($M$3:$M$11))-2),VLOOKUP(D3,$O$3:$P$11,2,0)))...

Note: Since not able to upload the sample file Ive mentioned the Rows and columns for better understanding.
 
Hi Irshath,


Thanks for raising your helping hand..


Hi Shri..


Finally I have to admit.. VBA is essential to save our kind of JOB.. :)

Just want to share..

[pre]
Code: 
Option Compare Text
Sub SetMargin()
Application.ScreenUpdating = False
Dim lookuprange As Range, checkrange As Range
Set lookuprange = [J2:N11]
Set checkrange = Cells(1, 1).CurrentRegion
For i = 2 To lookuprange.Rows.Count
For j = 2 To lookuprange.Columns.Count - 1
If Not IsEmpty(lookuprange.Cells(i, j)) Then
With checkrange
.AutoFilter 1, lookuprange.Cells(i, 1)
.AutoFilter j, lookuprange.Cells(i, j)
On Error Resume Next
.Range(.Cells(2, .Columns.Count), Cells(.Rows.Count, .Columns.Count)).SpecialCells(12).Value = _
lookuprange.Cells(i, lookuprange.Columns.Count)
On Error GoTo 0
.AutoFilter
End With
End If
Next j
Next i
End Sub
[/pre]

https://dl.dropbox.com/u/78831150/Excel/MultipleLookup.xlsm


Guidance and Suggestions are Welcome..


Regards,

Deb
 
Hi Deb,


I had not used DGET before but its style doesn't seem to fit the way the formula in your case needs to be copied down. Interesting formula.
 
Hi,


Cross-post & Solution is here..

http://www.get-digital-help.com/2013/03/01/lookup-using-multiple-conditions/#comment-53774


Regards,

Deb
 
You must log in or register to reply here.
Back
Top