If formula that compares Quantity before selecting a range to use

**KARENKERBA** · 10-17-2014, 01:54 PM

I don't know if I'm looking for the impossible, but...
I want to avoid creating a table to use a vlookup against. My poor spreadsheet already has enough of those.
I don't want to use VBA either.
This is what I need to do.

IF QUANTITY < 2
DETERMINE THE "ABC CODE" BASED ON COST BASED ON THE RANGE BELOW:
0 - 215 = C
216 - 1899 = B
1900 & GREATER = A

IF QUANTITY >= 2
DETERMINE THE "ABC CODE" BASED ON THE PRODUCT (QUANTITY * COST) FROM THE RANGE BELOW:
0 - 2099 = C
2100 - 17499 = B
17500 & GREATER = A

Any ideas?

**ChemistB** · 10-17-2014, 02:16 PM

=IF(Quantity<2, LOOKUP(Cost, {0,216,1900}, {"C","B","A"}), LOOKUP(Cost, {0,2100,17500}, {"C","B","A"}))

**Vikas_Gautam** · 10-17-2014, 03:04 PM

Sorry,
But a little Correction..
Here is the revised solution..
=IF(Quantity<2, LOOKUP(Cost, {0,216,1900}, {"C","B","A"}), LOOKUP(Cost*QUANTITY, {0,2100,17500}, {"C","B","A"}))

**ChemistB** · 10-17-2014, 03:21 PM

Nice catch Vikas

**Vikas_Gautam** · 10-18-2014, 12:07 AM

Thank you sir...

If formula that compares Quantity before selecting a range to use

LinkBack

Thread Tools

Rate This Thread

Display

If formula that compares Quantity before selecting a range to use

Re: If formula that compares Quantity before selecting a range to use

Re: If formula that compares Quantity before selecting a range to use

Re: If formula that compares Quantity before selecting a range to use

Re: If formula that compares Quantity before selecting a range to use

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] COUNTIF where criteria compares year only in the range

IF statement (non nested) that compares cell value against range

IF statement (non nested) that compares cell value against range

Selecting values from a list to add up to a specific quantity

Selecting amounts to display based on quantity

Bookmarks

Bookmarks

Posting Permissions