Dynamic lookup help

[bigbob5000]

Hi all,

I am familiar with vlookups and index match, but a new system has created a new problem that I have been unable to solve.

There is an automated report that spits out the below.

Column A - Roll up
Column B - Location
Row A - Account

A complicating factor is that the numbers of locations can vary in any given report (hence my dynamic comment), and are the same across roll ups but not all will repeat. What I need is to be able to "grab" the value from a Roll-up / location / account combo (or return 0 if its not showing up).

I am new here so apologies if this set up is confusing. Said differently to above, how can I pull VALUE above using "40100 - Location 1 - Account 3"

Account 1

Account 2

Account 3

Account 4

Account 5

Account 6

Account 7

Account 8

Account 9

Account 10

Account 11

Account 12

40100 - Roll up 1

Location 1

VALUE

Location 2

Location 3

Location 4

Location 5

Location 6

Location 7

Location 8

Location 9

Location 10

Location 11

Location 12

Location 13

Location 14

Location 15

40200 - Roll up 2

Location 1

Location 2

Location 3

Location 4

Location 5

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

****** id="cke_pastebin" style="position: absolute; top: 376px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

VALUE

<tbody>
</tbody>

</body>

 

[Misca]

Welcome to the msg board!

I'd rather do this with Power Query but if you want a formula this array formula should do the trick:

=INDEX(OFFSET($D$1,MATCH(TEXT(U8,"@"),LEFT(A2:A23,LEN(U8)),0),0,ROWS(A2:A23),COLUMNS(D1:O1)),MATCH(V8,OFFSET($A$1,MATCH(TEXT(U8,"@"),LEFT(A2:A23,LEN(U8)),0),,ROWS(A2:A23)),0),MATCH(W8,D1:O1,0))

Use Ctrl + Shift + Enter to enter it.

I copied your example table to A1:O23 and had my lookup values (Roll-up, Location, Account) in U8:W8.