Best match lookup.

[Brian Tam]

I’m trying to lookup UPC’s and have it tell me what is the best size (based on my criteria)

What I’m doing right now is entering the UPC.
Vlookup the Style #
Vlookup the Color
Vlookup the Size


Right now I’m stuck on how to get multiple results by matching both Style# and Color and giving me the available sizes.
After that I would like it to tell me if there is a better size (if so give me the size). If the one entered is the best size then say “True”

I gave the SIZES a value (I thought that might make it easier).

I’ve included an example of what I’m trying to accomplish and my weak attempt.

Thank for you help!
Brian

[Brian Tam]

Any advice or suggestions would be much appreciated.


Thanks,
Brian

[NBVC]

I don't understand how you determine a "better size"

Can you explain in detail each result you have in B4:B11 (assuming those are the desired results). What is the logic behind each result?

[Brian Tam]

I don't understand how you determine a "better size"

Can you explain in detail each result you have in B4:B11 (assuming those are the desired results). What is the logic behind each result?

Thank you for responding.

"better size" is base on table Q4:R4 I gave the size a value. I thought it would be easier to find the best match.

Yes, that is what I would like the results to be.
The "UPC" would be manually entered.

What I would like to happen is to have a column that tells me when entering the UPC if that UPC is the one I want "TRUE" if not tell me what size is the closest based on the "SIZE VALUE"

For the for the first entry (Cell A4), It is "TRUE" because (UPC 034758759088) matches (Style # + Color) and the smallest value Small=2. The other variations for size are Large=3 and XXLarge=4.

(Cell A8) UPC 084971743791

It is not "TRUE" because the size XXLarge=4 is not the smallest value. Medium=1 is the smallest value. So it says "Medium"

(Cell A11) UPC 731517834191

Is "TRUE" because it is the only item in the chart.


I hope that clarifies things. In my head this all makes sense.
The hurdle in my opinion is being able to look up all the different sizes when matching the style and color.

Any suggestions would be much appreciated.

Thanks,
Brian

[NBVC]

What I don't understand is why wouldn't all the results be medium then, since medium is assigned 1 (the smallest value)?

Why is B11 TRUE?

[Brian Tam]

What I don't understand is why wouldn't all the results be medium then, since medium is assigned 1 (the smallest value)?

Why is B11 TRUE?

B11 is True because it its the only style+color with a size.

The reason I need this is because we get all sizes in and we need to pull 1 size for each product. Style+Color.

I think all I need is a way to match both style and color. Get all the results. Then pull the sizes and retrieve the best size based on the list.

I'm sorry for the confusion.I really appreciate the time you have spent with me on this.

Thanks,
Brian

[NBVC]

Ok, I think I got you now....

If you can sort the table in Q4:R7 so that Q4:Q7 is sorted in ascending alphabetic order, then use this formula:

=IF(MIN(IF($K$4:$K$11=C4,LOOKUP($L$4:$L$11,$Q$4:$R$7)))=LOOKUP(D4,$Q$4:$R$7),TRUE,INDEX($Q$4:$Q$7,MATCH(MIN(IF($K$4:$K$11=C4,LOOKUP($L$4:$L$11,$Q$4:$R$7))),$R$4:$R$7,0)))

confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down.

The sample workbook attached includes conditional formatting to colour the TRUE and FALSE column per your original sample.... in case you need that too.

[Brian Tam]

Ok, I think I got you now....

If you can sort the table in Q4:R7 so that Q4:Q7 is sorted in ascending alphabetic order, then use this formula:

=IF(MIN(IF($K$4:$K$11=C4,LOOKUP($L$4:$L$11,$Q$4:$R$7)))=LOOKUP(D4,$Q$4:$R$7),TRUE,INDEX($Q$4:$Q$7,MATCH(MIN(IF($K$4:$K$11=C4,LOOKUP($L$4:$L$11,$Q$4:$R$7))),$R$4:$R$7,0)))

confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down.

The sample workbook attached includes conditional formatting to colour the TRUE and FALSE column per your original sample.... in case you need that too.

Thank you so much for this!


The only thing is that it doesn't lookup the style. I don't know where to enter that in the formula.


Thanks again,
Brian

[NBVC]

If you have the styles, say in adjacent column E, then try:

=IF(MIN(IF(($J$4:$J$11=E4)*($K$4:$K$11=C4),LOOKUP($L$4:$L$11,$Q$4:$R$7)))=LOOKUP(D4,$Q$4:$R$7),TRUE,INDEX($Q$4:$Q$7,MATCH(MIN(IF(($J$4:$J$11=E4)*($K$4:$K$11=C4),LOOKUP($L$4:$L$11,$Q$4:$R$7))),$R$4:$R$7,0)))

CSE key confirmed.