multiple column for lookup array

[ExcelNewbie2020]

Sirs,

Is there any short formula to look up a value from a multiple column lookup array? i know this can be also done by multiple vlookup or sumif but im looking for a much shorter formula..

In this table, Names are the lookup array and amount is the return value.. For excel version 2016

SAMPLE-FILE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	NAME	AMOUNT	NAME	AMOUNT	NAME	AMOUNT	NAME	AMOUNT
2	NAME1	10	NAME11	5	NAME21	100	NAME31	1			lookup value	AMOUNT
3	NAME2	20	NAME12	6	NAME22	200	NAME32	2			NAME5	50
4	NAME3	30	NAME13	7	NAME23	300	NAME33	3
5	NAME4	40	NAME14	8	NAME24	400	NAME34	4
6	NAME5	50	NAME15	9	NAME25	500	NAME35	5
7	NAME6	60	NAME16	10	NAME26	600	NAME36	6
8	NAME7	70	NAME17	11	NAME27	700	NAME37	7
9	NAME8	80	NAME18	12	NAME28	800	NAME38	8
10	NAME9	90	NAME19	13	NAME29	900	NAME39	9
11	NAME10	100	NAME20	14	NAME30	1000	NAME40	10
Sheet1

thank you very much

 

[Peter_SSs]

im looking for a much shorter formula

Why would you worry about the length of the formula, surely it is simply whether it returns the correct result & in an efficient manner?

i know this can be also done by ... sumif

.. and how much shorter are you likely to get?

23 03 05.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	NAME	AMOUNT	NAME	AMOUNT	NAME	AMOUNT	NAME	AMOUNT
2	NAME1	10	NAME11	5	NAME21	100	NAME31	1			lookup value	AMOUNT
3	NAME2	20	NAME12	6	NAME22	200	NAME32	2			NAME5	50
4	NAME3	30	NAME13	7	NAME23	300	NAME33	3
5	NAME4	40	NAME14	8	NAME24	400	NAME34	4
6	NAME5	50	NAME15	9	NAME25	500	NAME35	5
7	NAME6	60	NAME16	10	NAME26	600	NAME36	6
8	NAME7	70	NAME17	11	NAME27	700	NAME37	7
9	NAME8	80	NAME18	12	NAME28	800	NAME38	8
10	NAME9	90	NAME19	13	NAME29	900	NAME39	9
11	NAME10	100	NAME20	14	NAME30	1000	NAME40	10
SUMIF

Cell Formulas
Range	Formula
L3	L3	=SUMIF(A2:G11,K3,B2:H11)

If the return values were not numerical then it becomes a significantly more complex problem of course.

 

[ExcelNewbie2020]

Why would you worry about the length of the formula, surely it is simply whether it returns the correct result & in an efficient manner?


.. and how much shorter are you likely to get?

23 03 05.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	NAME	AMOUNT	NAME	AMOUNT	NAME	AMOUNT	NAME	AMOUNT
2	NAME1	10	NAME11	5	NAME21	100	NAME31	1			lookup value	AMOUNT
3	NAME2	20	NAME12	6	NAME22	200	NAME32	2			NAME5	50
4	NAME3	30	NAME13	7	NAME23	300	NAME33	3
5	NAME4	40	NAME14	8	NAME24	400	NAME34	4
6	NAME5	50	NAME15	9	NAME25	500	NAME35	5
7	NAME6	60	NAME16	10	NAME26	600	NAME36	6
8	NAME7	70	NAME17	11	NAME27	700	NAME37	7
9	NAME8	80	NAME18	12	NAME28	800	NAME38	8
10	NAME9	90	NAME19	13	NAME29	900	NAME39	9
11	NAME10	100	NAME20	14	NAME30	1000	NAME40	10
SUMIF

Cell Formulas
Range	Formula
L3	L3	=SUMIF(A2:G11,K3,B2:H11)

If the return values were not numerical then it becomes a significantly more complex problem of course.

i have no idea that sumif can do that.. i'm thinking of =sumif()+sumif()....... thank you very much sir

 

[Peter_SSs]

You're welcome.

Note that it does require that the name being looked up will not occur more than once in the data if you want a simple lookup rather than a sum.

 

[ExcelNewbie2020]

You're welcome.

Note that it does require that the name being looked up will not occur more than once in the data if you want a simple lookup rather than a sum.

thanks sir, just a bit curious, what if the return value is a date instead of amount?..does sumif can do that?

 

[Peter_SSs]

Date will be fine so long as it is a 'real' date (numerical) & not a text date. You may have to format the formula cell with your desired date format.

23 03 05.xlsm
	A	B	C	D					I	J	K	L
1	NAME	AMOUNT	NAME	AMOUNT
2	NAME1	10	NAME11	5							lookup value	AMOUNT
3	NAME2	20	NAME12	6							NAME5	15/06/2023
4	NAME3	30	NAME13	7
5	NAME4	40	NAME14	8
6	NAME5	15/06/2023	NAME15	9
7	NAME6	60	NAME16	10
8	NAME7	70	NAME17	11
9	NAME8	80	NAME18	12
10	NAME9	90	NAME19	13
11	NAME10	100	NAME20	14
SUMIF

Cell Formulas
Range	Formula
L3	L3	=SUMIF(A2:G11,K3,B2:H11)

 

[shinigamilight]

=MAX(IF(A2:H11=K3,B2:I11))