INDEX Match not working, advice

[realrookie]

Hello,

I am trying to get my INDEX Match to work but somehow it does not return the desired value.

I have attached a small sample.

I want to return the value from where a name in Column A and date row 1 intersect.

E.g.: Name3 with the date in F1 would return 1649.5

{=INDEX(B2:L19,MATCH(B2,A2:A19=B2)*(D26,B1:L1=D26),0)} is the formula I used (and yes I entered it using crtl shift enter)

Any advice or simpler solution greatly appreciated.

RR

[zbor]

Don't confuse "not working" with "wrong usage"

Please Login or Register  to view this content.

[realrookie]

@zbor,

Thank you very much for clarifying that

On my actual workbook, the data is retrieved from a different sheet. So All I do is move the range (B2:L19) towards the sheets and enter as array?

[FlameRetired]

Hi realrookie,

zbor's formula won't require Ctrl + Shift + Enter. Enter is sufficient.

[realrookie]

Thank you both

[realrookie]

Sorry for "unsolving" the thread.

The first import is fine, all data required is shown as expected.
When I import a second file (which adds data to the target list), the formula changes...

Please Login or Register  to view this content.

This basically change to

Please Login or Register  to view this content.

Is there a way to make sure the formula does not change when data is added ( the macro adds a column...)

[zbor]

Is there a way to make sure the formula does not change when data is added ( the macro adds a column...)

That would depend on macro.

[realrookie]

ok...

The first macro sorts all the data by date by person

Please Login or Register  to view this content.

The second one transfers it to the relevant sheets.

Please Login or Register  to view this content.

All r39 are used and there are 6 of those running after each other.

On an extra sheet we had a graph, which would show the fluctuation. Now higher up has requested to see this on a weekly basis, so including dates.

I added a "user" with the name ImportDate and across all the sheets, he/she is on top and adds the dates in, hence I used the index match to return the data corresponding to the user of the requested date...
(hope this makes sense)