• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Is there a SMALL formula for letters?

C

cnaq

New Member
I am using and INDEX/MATCH formula to look up the vendor from a products purchased report and match it with products from a product sold report. The vendor data I am looking up begins with a letter(ex. TH10) I have no problem pulling in one vendor of a product. The problem is that some products are purchased from multiple vendors. I need a formula to look up the next vendor. I think the following formula example I found would work for me if the vendor names were numbers.


If you wanted to return the quantity value for the second occurrence of apple, you would use the following array formula:


=INDEX(A2:C6,SMALL(IF(A2:C6="apple",ROW(A2:C6)-ROW(A2)+1,ROW(C6)+1),2),2)


Is there a similar formula to the "SMALL" formula I could substitute that would look up the next vendor based on the alphabet as SMALL does to numbers?


Thanks
 
Rethinking the approach:


=INDEX(ListOfVendors,SMALL(IF(ListOfProduct="apple",ROW(ListOfProduct),ROW(A1)))


Confirm this as an array formula (Ctrl+Shift+Enter), then copy down to get a complete list of all vendors that sell that product.


You might want to check out my article here:

http://chandoo.org/wp/2011/11/18/formula-forensics-003/
 
You must log in or register to reply here.
Back
Top