Hi guys, I am trying to put a formulae together that incorporates a subcategory when I do a vlookup. I have a supplier code, each supplier has a product group, this group then has a discount associated to it. I am trying to put into one formulae the search of a supplier code and group if there is one to show the discount in place. Some product groups also cover a few suppliers because they are generic items. the vlookup I am using is here:
=(VLOOKUP(H5,'DISCOUNTS PER GROUP'!$A$1:$J$2431,8,FALSE))
say my supplier code is 1 it may have discount groups a, b, c attached to it, supplier 2 might have discount groups b, c, d. I have built a sheet that has each supplier with the discount codes within them. so I have duplicated the discount groups.
Thank you in advance guys
SB
There are a few of methods. The first method is to use INDEX and MATCH, along with concatenation, to search multiple columns. This is an array formula, however, and so can slow the workbook down if used too much. Also, you should not reference entire columns (and cannot if using Excel 2003 or earlier). The link below will give you details
http://support.microsoft.com/kb/59482
The second method still uses VLOOKUP, but requires you to create a "helper column" to concatenate the desired columns/fields.
The links below will detail the various methods.
http://chandoo.org/wp/2010/11/02/mul...dition-lookup/
http://www.dailydoseofexcel.com/arch...n-two-columns/
Note: INDEX/MATCH and VLOOKUP will allow for the return of a string, whereas SUMPRODUCT and SUM Array Formula will only work with numeric values.
Last edited by Whizbang; 11-28-2011 at 11:55 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks