Lookup value from reversed columns and rows

szakharov7723

Board Regular
Joined
Jun 22, 2018
Messages
85
Office Version
  1. 2019
Platform
  1. Windows
Source table
tine 2tine 2tine 2Hitte 3Hitte 3Hitte 3
PM assignedWO assignedTask assignedPM assignedWO assignedTask assigned
18-May6104800
19-May6.51048096
20-May7112484864
21-May7.571224802.56
22-May41064160
23-May4.511872165.76
24-May51020803.26.4

<colgroup><col><col span="5"><col></colgroup><tbody>
</tbody>

Table to implement lookup

18-May19-May20-May21-May22-May23-May24-May
tine 2PM assigned
tine 2WO assigned
tine 2Task assigned
Hitte 3PM assigned
Hitte 3WO assigned
Hitte 3Task assigned

<colgroup><col><col><col span="2"><col><col span="3"><col></colgroup><tbody>
</tbody>

This is only an example of a large database I have with bunch of other formulas. So I cannot change the structure of tables, especially 2nd one.
I need to find a way to create a look up for 2nd table. Both tables are not static, as new locations can added meaning 1st table can extend in columns, while 2nd table will be extended in rows. When this happens I would like a casual user to have no problem extending the formula for new locations.
So far I tried Sumproduct and IndexMatch : both didn't work out, however I could make a mistake somewhere. I tried to Concatenate location and assigned, but still didn't get any good result.
VBA might be a solution, but I would still prefer a formula approach.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Here's one way:


Excel 2010
ABCDEFGHIJ
1
2tine 2tine 2tine 2Hitte 3Hitte 3Hitte 3
3PM assignedWO assignedTask assignedPM assignedWO assignedTask assigned
418-May6104800
519-May6.51048096
620-May7112484864
721-May7.571224802.56
822-May41064160
923-May4.511872165.76
1024-May51020803.26.4
11
12
13
14
15
16
17
18
19
2018-May19-May20-May21-May22-May23-May24-May
21tine 2PM assigned66.577.544.55
22tine 2WO assigned11171110
23tine 2Task assigned00121201820
24Hitte 3PM assigned48484824647280
25Hitte 3WO assigned00488016163.2
26Hitte 3Task assigned096642.5605.766.4
Sheet1
Cell Formulas
RangeFormula
D21{=INDEX($C$4:$H$10,MATCH(D$20,$B$4:$B$10,0), MATCH(1,($C$2:$H$2=$B21)*($C$3:$H$3=$C21),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0
Is it possible to create formula that would sum values by several dates for each location and assigned in the same case ?
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top