Dear,
I would like to display the content of one cell. the lookup is dependent on multiple (in added example 2 variables in colums and 1 in a row)
Can any one help with a this without using a helper column or row.
regards
Dear,
I would like to display the content of one cell. the lookup is dependent on multiple (in added example 2 variables in colums and 1 in a row)
Can any one help with a this without using a helper column or row.
regards
hi jaapaap. try:
=SUMIFS(INDEX(C2:Y7,0,MATCH(E19,C1:Y1)),A2:A7,C19,B2:B7,D19)
or an array formula:
=INDEX(C2:Y7,MATCH(C19&"_"&D19,A2:A7&"_"&B2:B7,0),MATCH(E19,C1:Y1,0))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
thanks a lot. This works.
Or with a pivot table (after re-arange the data).
See the attached file.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
@benishiryo
is it possible that te function does not work when you format as table. See attachment.
@jaapaap:
Table doesn't accept numbers as the heading, so it converts them into texts. a simple addition in red will suffice, to find the number as a text:
=INDEX(C2:Y7,MATCH(C19&"_"&D19,A2:A7&"_"&B2:B7,0),MATCH(E19&"",C1:Y1,0))
ok thanks, did not knew this
@jaapaap
you did not reply on my solution in #4.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks