Unable to retrieve an item from a list that meets multiple criteria

**thomashasler** · 03-20-2015, 10:51 AM

I’m attempting to create a formula where I’m matching two different criteria and returning a value. The one criteria is a SKU number and the other is a tier of values based on the quantity of a particular SKU. Here’s the formula I’m using, but it’s not returning the correct value. {=INDEX($P$2:$P$1486,MATCH(S1&U1,$B$2:$B$1486&$E$2:$E$1486,1))}. Cell S1 is the SKU lookup value and U1 is the quantity lookup value. Column “P” provides the value based on the SKU # and the quantity ordered. Column "B" is a list of SKU's and column "E" are the various tiers, e.g. 1-500, 501- 1000, etc. Thank you for your help.

**mcmahobt** · 03-20-2015, 10:57 AM

Since this is an array formula, did you enter it using CTRL+SHIFT+ENTER? I know you have the brackets around your formula, but typing the brackets doesn't turn the formula into an array formula. Confirming with CSE does.

**thomashasler** · 03-20-2015, 11:07 AM

Yes, I did enter it using CTRL + SHIFT + ENTER

**mcmahobt** · 03-20-2015, 11:11 AM

Then by the looks of it, I'd bet it's the tiers that is throwing off your results. However, without a sample workbook there's not much I can say beyond that.

**thomashasler** · 03-23-2015, 08:13 AM

I'm sure you're right about the tiers. Is there anyway around this problem? I'm attaching a sample of the date I'm using.

**mcmahobt** · 03-23-2015, 09:43 AM

Try this instead, also entered as an array.

Make sure that your value stored in M4 is also being stored as a number, not as text.

=INDEX($H$2:$H$1486,SMALL(IF(--(($A$2:$A$1486=K4)*(M4>=$B$2:$B$1486)*(M4<$C$2:$C$1486)),ROW($H$2:$H$1486)-1),ROWS($1:1)))

**thomashasler** · 03-23-2015, 10:28 AM

This worked beautifully. Thanks so much for your help!

Unable to retrieve an item from a list that meets multiple criteria

LinkBack

Thread Tools

Rate This Thread

Display

Unable to retrieve an item from a list that meets multiple criteria

Re: Unable to retrieve an item from a list that meets multiple criteria

Re: Unable to retrieve an item from a list that meets multiple criteria

Re: Unable to retrieve an item from a list that meets multiple criteria

Re: Unable to retrieve an item from a list that meets multiple criteria

Re: Unable to retrieve an item from a list that meets multiple criteria

Re: Unable to retrieve an item from a list that meets multiple criteria

Thread Information

Users Browsing this Thread

Similar Threads

Retrieve the the value from list that meets criteria

problem with tip "Retrieving an Item from a List that Meets Multiple Criteria"

Retrieving an Item from a List that Meets Multiple Criteria

Retrieving an Item from a List that Meets Multiple Criteria

Retrieving an Item from a List that Meets Multiple Criteria

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions