Perform lookup of every cell in a range using Excel (for Mac)

1
votes

Given a row in Excel with variable number of columns populated with item numbers, I'd like to do a lookup in another table listing the cost for each item in the populated columns, and sum the total cost. My Google-fu has failed me as I can't seem to find any way to perform an Excel formula (VLOOKUP in this case) on every cell in a range. Basically, if I have the following tables:

ItemCosts Item | Cost A | 10 B | 20 C | 40

Orders Order | 1 | A 2 | A | B 3 | B | C

How can I calculate the total in the following table given an order number?

Order Summary Order | Total 1 | 10 2 | 30 3 | 60

I've managed to write some formulas that generate the lookup range in the form Orders!$B$2:$C$2. I thought I could use that range as the input to some formula to map the items in the order to their item costs and sum them. I had hoped I could perform a VLOOKUP on every item in the range and it could return a range from the ItemCosts table that I could then sum but VLOOKUP only takes a single cell as the lookup value. I feel like I'm missing something obvious. Should I just do it in VBA? This answer mentions that VLOOKUP can take an array and then return an array but I couldn't reproduce it in Excel 2016 for Mac. It just returns the first match as a single value.

EDIT: Based on the comments it seems like the unnormalized Orders table might be a problem. But even if it looks like this, it's not clear to me how to perform a lookup of each item's cost so I can calculate the total.

Orders Order | Item 1 | A 2 | A 2 | B 3 | B 3 | C

1
excelexcel-formulams-officeexcel-2016
Your current data layout is not normalized. What I mean by this, by example, is that suppose a new order comes along with 5 items. Then, you'd have to make a big change in your spreadsheet. A better design, I think, would be to have the orders data with each order-item taking up one row. Then, you could handle it within Excel using either formula, or, better yet, a pivot table with rollup. – Tim Biegeleisen
I see your point but an order with 5 items doesn't necessarily require a change to the spreadsheet as I've managed to dynamically calculate the range containing the items using COUNTA. – Rotsiser Mho
As you wish, but if I were you, I'd normalize orders and then just hit it with a pivot table. – Tim Biegeleisen
OK, let's say I do that. I'm still left with a range of items over which I need to look up each item's cost. How do I do that with formulas or a pivot table? I'm not too familiar with pivot tables and when I tried to create a relational data model I discovered that Excel for Mac doesn't support them. – Rotsiser Mho
Is the | representing a new column ? – QHarr

1 Answers

1
votes

There are two ways of producing your required output with "standard formulas", which I am going to propose. I imagine, that there possibly is an array function that could solve this without helper columns/sheets, but they tend to be complex and hard to maintain.

For the normalized version you cound do the following:

1) Add a value column to the orders Table:

'Column of Cost in ItemCost Table' = ColumnCost
'Column of Item in ItemCost Table' = ColumnItem

Order | Item | Value
1     | A    |=index(ColumnCost, match(ItemCell, ColumnItem, 0))
2     | A    |...
2     | B    |...
3     | B    |...
3     | C    |...

2) Use a sumif formula to sum over the Value column in the order table conditioned on the order number.

For the unnormalized version instead of a helper column you can use a helper sheet, of the same form and dimensions as the orders sheet.

Using the same structure of index formula as in the above example, but instead of using the ItemCell in the order sheet. It will look as follows:

Orders 

Order |
1     | A
2     | A | B
3     | B | C

Orders Helper

Order |
1     | 10
2     | 10 | 20
3     | 20 | 40

For the summary sheet you just sum over the respective column.

You can find an example of this here: https://ufile.io/rbxf1