Looking up multiple results based on two criteria -

[Still Learning UK]

Hi,

Hope someone may be able to help with this, just can't figure it out but I suspect it's not massively complicated. I want to look up a range of values based on two variable criteria as below:

Excel.jpg

Very much appreciate if someone can point me in the right direction.

Thank You!

[mcmahobt]

Try entering this in cell B5 as an array formula and confirm with CTRL+SHIFT+ENTER:

Please Login or Register  to view this content.

and copy down to fill.

[Pete_UK]

Put this formula in B5:

=INDEX($C$17:$K$22,MATCH("Supplier "&$B$3,$A$17:$A$22,0)+IF($B$4<500,0,1),MATCH($A5,$C$16:$K$16,0))

then copy down into B6:B13

Hope this helps.

Pete

[Still Learning UK]

Thank you both very much, each one works fine but I opted for Pete's as it's a bit shorter and easier for my mind to digest.

Pete, being cheeky now but could you explain the formula briefly for me perhaps?

Very much appreciate your help.

[Still Learning UK]

Pete,

One last thing, if the quantity entered is less than 350, would it be opossible for the expression "Below Minimum" to be returned?

Also, if the supplier minimum values are different how could this be accounted for in the formula?

Thank you.

[Pete_UK]

The INDEX function allows you to look at a table (C17:K22 in your case) and return a single value from that depending on the row and column parameters, so its syntax is basically:

INDEX(table, row, column)

The column parameter in my formula is thus:

MATCH($A5,$C$16:$K$16,0)

and this is looking to see where A5 can be found in the range of headings in C16:K16 - this will return 1 in this instance, as A5 is Option 1 and this appears in cell C16, which is the first cell in the range, but as the formula is copied down this term will return 2, then 3 etc. on successive rows, even though the data is arranged horizontally.

The row expression is a bit more complex:

MATCH("Supplier "&$B$3,$A$17:$A$22,0)+IF($B$4<500,0,1)

The data in the range A17:A22 contains the word "Supplier ", so this is added on to your choice from cell B3 and then finds the row where it matches. However, the MATCH function will return the first row where a match is found, but you have two of each Supplier because of the Quantity column - the IF function then adds either a zero or a one onto the MATCH value, depending on the value in cell B4.

Hope this helps.

Pete

[Pete_UK]

By the way, if that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[Pete_UK]

Ah, I've just seen that you have slipped in another post before I gave you the explanation of how the formula works.

You could have a table which listed each supplier and the minimum, and then use this in the formula.

It would help if you attached a sample Excel workbook, rather than a picture of one, so I can work on the formulae directly in your file.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Please note that the Paperclip icon does not work.

Pete

[Still Learning UK]

Hi Pete,

Thanks for getting back to me, hopefully attached is the file in question with a little modified data for the minimums for suppliers changed.

Thanks

[Pete_UK]

I just compiled a long reply to you giving the necessary changes to the formula (in red), and explanations, and then the firewall clicked in and removed the thread (because of supposed HTML content - angle brackets !!). So, I've just attached the file, and you can see what changes have been necessary to the formula in C12 (copied down). I'll pick the changes out in another post.

Hope this helps.

Pete

P.S. Thanks for the rep, by the way.

[Pete_UK]

The first part of the formula has had this added:

IF($B$11 < VLOOKUP("Supplier "&$B$10,$A$2:$B$7,2,0),"Below Minimum",

with a corresponding close-bracket at the end of the formula. This checks that the value in B11 is at least the minimum for the chosen supplier.

In addition, I noted that you had changed the second value for Supplier C, so I presume their discounting is only given for larger quantities. Consequently, I have had to change the middle part of the formula:

IF($B$11 < 500,0,1)

in the inner IF function to this:

IF($B$11 < INDEX($B$2:$B$7,MATCH("Supplier "&$B$10,$A$2:$A$7,0)+1),0,1)

in order to get the second-row value for the chosen supplier rather than the fixed value of 500.

Hope this helps.

Pete

[Still Learning UK]

You are the man!

Mucho Grassyarse ..........

[Pete_UK]

... but I suspect it's not massively complicated...

What do you think now ??

Glad we got it all sorted in the end.

Pete

[Still Learning UK]

Yes, indeed it is rather beyond my current skill set :-) but I'm hoping this forum will help me improve. I'm presently a bit like Eric Morecambe - all the right formulas but not necessarily in the right order

Thanks a million, extremely helpful