I am trying to display the lowest priced item for many products set out in blocks of three comparative suppliers, all within a single worksheet. (See attached sample.)
The formula in column H works okay as long as the minimum price in column E for each block of 3 cells is in the first or middle row of the referenced range. If the lowest price in in the third cell of the range, the formula returns #N/A.
Any suggestions or assistance would be greatly appreciated! Thanks in advance.
Trish in Oz
-------------
A problem well defined is a puzzle half solved
If you attach a sample of your workbook it will be easier to find a solution.Remember to remove/replace sensitive data before uploading the file. Look here if you need help with attachments:
http://www.excelforum.com/faq.php?fa...b3_attachments
You need to add the match_type in unordered lists.
See the MATCH help for more details but here is the relevant paragraph:
So your formula should look like:If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.
If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.
downforce
Please Login or Register to view this content.
Thanks a lot, downforce! That works exactly the way I need it to.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks