Combine VLOOKUP and HLOOKUP
Solved
cg.c
Posted messages
196
Registration date
Status
Member
Last intervention
-
cg.c Posted messages 196 Registration date Status Member Last intervention -
cg.c Posted messages 196 Registration date Status Member Last intervention -
Hello,
I would like to know if it is possible to "compile" a VLOOKUP with an HLOOKUP?
Statement:
(Savings question.)
In sheet 1 of the workbook: the summary to print.
In sheet 2: column A (A1:A13), the months.
Row 1 (B1:X1...) the names of the people.
Find in sheet 1, column C (savings) the amount indicated in sheet 2 depending on the month (sheet 1, $E$5), and the name (sheet 1, column A).
Attached is my attempt at a table:
https://www.cjoint.com/c/EIBnjz7BsFa
So far I can only do it using VLOOKUP by changing the column index number for each...
Is there a more "automatic" way???
Thank you
Cg.c
I would like to know if it is possible to "compile" a VLOOKUP with an HLOOKUP?
Statement:
(Savings question.)
In sheet 1 of the workbook: the summary to print.
In sheet 2: column A (A1:A13), the months.
Row 1 (B1:X1...) the names of the people.
Find in sheet 1, column C (savings) the amount indicated in sheet 2 depending on the month (sheet 1, $E$5), and the name (sheet 1, column A).
Attached is my attempt at a table:
https://www.cjoint.com/c/EIBnjz7BsFa
So far I can only do it using VLOOKUP by changing the column index number for each...
Is there a more "automatic" way???
Thank you
Cg.c
1 answer
-
Hello,
Yes, using the combination of INDEX with MATCH
INDEX will look up in a matrix at a given row and a given column
We find the sought row (the one for the month) with MATCH, the same for the sought column (the name)
Formula to put in C9 and drag down
=INDEX(Sheet2!$B$2:$F$13,MATCH($E$5,Sheet2!$A$2:$A$13,0),MATCH(A9,Sheet2!$B$1:$F$1,0))
Best regards
Via
--
"Imagination is more important than knowledge." A. Einstein
