How to return list of value based on another list of values-CodePudding

I am wondering if there is a way to return a list of data similar to vlookup. exactly it is return a list of value instead of single value.

for example. I have a table with headers

A	B	C	E	C
1	0	0	1	0

and another table with value I am trying to look up

Type	Cost
A	34
B	10
C	2
E	5
C	9

What I wanted to do is to get the cost of each type in my table 1 and do sumproduct of the value

so the end result would be A - 341 E - 51, which is 39

wonder if there is a way for me to pull the reference value based on the entire list of my header and results output like 34, 10,2,5,9

I can get my result by addition and multiplication. but that will be a lot of work, or vlookup for very single type to get each cost and both will result in

341 100 20 51 9*0

but don't think this is sufficient enough for me.

if you guys have any idea, thank you in advance for the help!

CodePudding user response：

If you have Excel 365 you can use this formula:

=LET(Types,A1:E1,
Counts,A2:E2,
Lookup,I2:J6,
Costs,MAP(Types,LAMBDA(v,INDEX(Lookup,MATCH(v,CHOOSECOLS(Lookup,1),0),2))),
SUM(Costs*Counts))

CodePudding user response：

`SUM` Up an `XLOOKUP`

SUM/XLOOKUP

=SUM(XLOOKUP(Sheet2!A2:A6,Sheet1!A1:E1,Sheet1!A2:E2,0)*Sheet2!B2:B6)
=SUM(XLOOKUP(Sheet1!A1:E1,Sheet2!A2:A6,Sheet2!B2:B6,0)*Sheet1!A2:E2)

LET

=LET(DataCols,Sheet2!A2:B6,MultiRows,Sheet1!A1:E2,
    lData,TAKE(DataCols,,1),vData,TAKE(DataCols,,-1),
    lMulti,TAKE(MultiRows,1),vMulti,TAKE(MultiRows,-1),
SUM(XLOOKUP(lData,lMulti,vMulti,0)*vData))

or the other way around (see the last line).

=LET(DataCols,Sheet2!A2:B6,MultiRows,Sheet1!A1:E2,
    lData,TAKE(DataCols,,1),vData,TAKE(DataCols,,-1),
    lMulti,TAKE(MultiRows,1),vMulti,TAKE(MultiRows,-1),
SUM(XLOOKUP(lMulti,lData,vData,0)*vMulti))

How does XLOOKUP(lData,lMulti,vMulti,0) work...

... since lData is a column, while lMulti and vMulti are rows?

In simple words, here is how I'm seeing what is happening:
- Create an array of size lData.
- Loop through each cell (element) of lData and attempt to find a match in lMulti.
- If found return the associated value, the value at the same index (column in this case), from vMulti and put it in the array.
- If not found, put a zero instead (4th parameter; error if omitted).
- When done, return the array.
To conclude, only the 2nd and 3rd parameters (ranges, arrays) of XLOOKUP need to be of the same size while the result will always be an array of the same size as the 1st parameter (range, array).

Why use LET...

... since the formula becomes much longer?

The formula actually becomes more readable (once you get used to it) and often more efficient.
Also, it could be considered the first step in getting used to using the LAMBDA function to create your own functions.

Consider this:

=LAMBDA(DataCols,MultiRows,LET(
    lData,TAKE(DataCols,,1),vData,TAKE(DataCols,,-1),
    lMulti,TAKE(MultiRows,1),vMulti,TAKE(MultiRows,-1), 
SUM(XLOOKUP(lData,lMulti,vMulti,0)*vData)))(Sheet2!A2:B6,Sheet1!A1:E2)

You can use the part before the last parentheses...

=LAMBDA(DataCols,MultiRows,LET(
    lData,TAKE(DataCols,,1),vData,TAKE(DataCols,,-1),
    lMulti,TAKE(MultiRows,1),vMulti,TAKE(MultiRows,-1), 
SUM(XLOOKUP(lData,lMulti,vMulti,0)*vData)))

... to create a name, e.g. MultiLookup.

Now, in this workbook, you can use the new function with the simple...
```
=MultiLookup(Sheet2!A2:B6,Sheet1!A1:E2)
```
... where you can easily use it for different ranges.

SUM Up an XLOOKUP

`SUM` Up an `XLOOKUP`