Index/Match rounding up

[searnold56]

Hello -

I used index and match logic to lookup a SKU from a db of skus with 5 separate criteria. The only numbered criteria is rounding a whole number up to the next nearest whole number. I tried a -1 (see below, pulling off of C4) which works like a champ unless my quantity is 1, then it pulls nothing.

=INDEX('SKU DB'!A2:H1201,MATCH(1,('SKU DB'!B:B=B4)*('SKU DB'!A:A=(C4-1))*('SKU DB'!C:C=D4)*('SKU DB'!D:D=E4)*('SKU DB'!I:I=A4),0),7)

Any suggestions?

Thank you!

 

[Aladin Akyurek]

What is your intent? Does column C house quantities?

 

[searnold56]

What is your intent? Does column C house quantities?

Thank you for the reply!

Yes, C4 has a drop down of whole numbers 1 through 50 and column A on the SKU DB worksheet has 24 sets of 1-50.

[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Service tier[/TD]
[TD]# of TB[/TD]
[TD]Agreement Term[/TD]
[TD]Paid monthly, Paid in full[/TD]
[TD]SKU[/TD]
[/TR]
[TR]
[TD]BU[/TD]
[TD]Silver[/TD]
[TD]6[/TD]
[TD]12 months[/TD]
[TD]Paid in full[/TD]
[TD]BU-VCC-SS-7TB-12M[/TD]
[/TR]
</tbody>[/TABLE]


If the above criteria is selected (where the 6 resides is c4), the SKU matches in every spot except it's rounding up from one whole number to the next whole number. I need it to return an exact match.

 

[Aladin Akyurek]

Still unclear...

=INDEX('SKU DB'!A2:H1201,MATCH(1,('SKU DB'!B:B=B4)*('SKU DB'!A:A=(C4-1))*('SKU DB'!C:C=D4)*('SKU DB'!D:D=E4)*('SKU DB'!I:I=A4),0),7)

better:

=INDEX('SKU DB'!G2:G1201,MATCH(1,('SKU DB'!B2:B1201=B4)*('SKU DB'!A2:A1201=C4)*('SKU DB'!C2:C1201=D4)*('SKU DB'!D2:D1201=E4)*('SKU DB'!I2:I1201=A4),0))

wouldn't round up anything. Rather, it returns a result from column G if all of the conditions hold.

 

[searnold56]

That worked like a champ! Thank you!

 

[Aladin Akyurek]

That worked like a champ! Thank you!

You are welcome.