Excel

EXCELISFUN Tutorial RECORD.FIELD Please refer to the video by EXCELISFUN. 1) Use two xlookups where 1st one is returning the row and the second gives you the column array 2) Use Power Query Have two query connections for 2 tables For the lookup table, add a custom column that outputs the table with amounts In the custom column, first have it find the vendor code row Then outside of that, use Record.Field to refer to that row field and return the city amount

Index/Match 2 Way Xlookup Website:  https://www.excel-easy.com/examples/two-way-lookup.html Match:  https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a Index:  https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd Below I use MATCH function to find the row and column number of a certain month or certain Pokemon Plushy. Then, I use the INDEX function to find the insertion of the row and number and that gives us the sales for that Pokémon Plushy for that month.

Xlookup Syntax:  https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929 Below is an example of using a 2 way Xlookup that I learned from Linkedin Learning.  1) Use xlookup to find the price of item xlookup(item we are looking for, item array from price grid, output price array from price grid) 2) Use 2 way xlookup to find the discount First xlookup the item against the item array from price grid Nested within is another xlookup the quantity against the quantity array from price grid Output the discount table where item and quantity intersect  match mode is -1 (exact match or next smaller numbe 3) Find the cost by finding the product of the price, quantity, and discount