Using Index Match to return value greater than ...

[FredrikLi]

Hello,

I am trying to solve the following in Excel 2013. Using the below table...

Product	Length	Segment
Alfa	200	C
Alfa	250	C (to be reassigned to D)
Beta	200	C
Beta	250	D
Charlie	150	B
Charlie	250	B

<tbody>
</tbody>

... I would like to reassign certain Products with certain Lengths to their correct Segments. As in:
- Both Alfa Products are listed as belonging to Segment C but if the length is greater than 225 then that Alfa product is to be reassigned to Segment D.
- Same goes for the Beta Products however here the Segments are correct from the start.
- And it also depends on the product. Ie for the Charlie Products they stay in Segment B even though the length may be greater than 225.

I already use Vlookup for the above when it suffices to just check what the product is, but here I suspect that Vlookup falls short. So I was looking at some similar cases and was thinking of using Index Match but I can't think of how. Or maybe there is a better idea?

Thanks for any help

 

[DRSteele]

I think this might do what you ask.

	A	B	C
1	Product	Length	Assigned Segment
2	Alfa	200	C
3	Alfa	250	D
4	Beta	200	C
5	Beta	250	D
6	Charlie	150	B
7	Charlie	250	B

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet63​

Worksheet Formulas Cell Formula C2 =IF(A2="Charlie","B",IF(B2>225,"D","C")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[FredrikLi]

Thanks!

But I realize I should have specified that the above is just an example and that there are more than 100 Products (or Product types, really) and around 15 segments, and that these will change/have to be updated over time. Also the length may not necessarily be greater/less than 225, it may be greater/less than whatever is decided depending on the Segment.

At the moment I use vlookup to reassign products to their segments. The data is in one sheet and in the Segment field there is a vlookup looking in a table in another sheet: =IF(ISERROR(VLOOKUP(A2;Tbl_Segment;2;FALSE));C2;VLOOKUP(A2;Tbl_Segment;2;FALSE)). Ie the Product is in A2 and if the vlookup finds the corresponding Product in the Segment table it will assign the new Segment, if the vlookup doesn't find anything it will keep the Segment (in C2) that was there from the start.

However with the Segment also being dependent on length it makes it more difficult. What I want to achieve is:
- In some cases keep the Segment if the Product belongs to it no matter what
- In other cases separate/break out the Product into a new or another Segment if we decide its Length requires it
- I was thinking of some index match formula looking into a Product/Segment/Length table but I'm falling short of how to do it

I hope I'm being clear, if not I can provide a larger data sample so as to illustrate.

And once again, thanks for any help

 

[FredrikLi]

Bump!

 

[AliGW]

What does your lookup table look like?

 

[FredrikLi]

Like this:

Product	Segment
Alfa	C
Beta	C
Charlie	B
And so on

<tbody>
</tbody>

The table is named Tbl_Segment as in the formula I posted before.

At the moment 50 different products are reassigned to 4 new segments. In total however there are a lot more products and 10 segments.

 

[AliGW]

So you need to create a lookup table that accounts for the value differences. Create that first and then we can tell you how to look up the values using a formula.

 

[FredrikLi]

Fair enough. Below is my proposed lookup table and after that my product table which I've expanded a bit from before, just to be safe.

Lookup table

Product	Segment	Length
Alfa	C	=<225
Alfa	D	>225
Beta	C	=<225
Beta	D	>225
Charlie	B

<tbody>
</tbody>

Product table

Product	Segment	Length
Alfa	C	250
Alfa	C	200
Beta	C	200
Beta	D	200
Beta	D	250
Charlie	B	200
Charlie	C	250

<tbody>
</tbody>

Meaning:
- Product Charlie goes into segment B no matter its length
- Product Alfa goes into Segment C if it's no more than 225 in length, and segment D if it's more than 225 in length
- Product Beta same as Alfa


I hope this is clear enough. Once again, there are at the moment a lot more products than Alfa, Beta and Charlie and there are a total of 10 Segments.

 

[AliGW]

Try this:

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G
1	Lookup table
2	Product	Segment	Length			0	226
3	Alfa	C	0		Alfa	C	D
4	Alfa	D	226		Beta	C	D
5	Beta	C	0		Charlie	B	B
6	Beta	D	226
7	Charlie	B	0
8
9
10
11	Product table
12	Product	Segment	Length
13	Alfa	D	250
14	Alfa	C	200
15	Beta	C	200
16	Beta	C	200
17	Beta	D	250
18	Charlie	B	200
19	Charlie	B	250
Sheet1

Cell Formulas
Range	Formula
B13	=INDEX($F$3:$G$5,MATCH(A13,$E$3:$E$5,0),MATCH(C13,$F$2:$G$2,1))