Structured Referencing tables

[Tom_J_W]

Hello,

I'm looking to reference a specific cell in a table in a similar manner to how =getpivot data works (working off the intersect of a row and column) however I don't understand the structured refencing page in excel's internal help.

ZA010155687.GIF

Say I wanted to return the value for the intersection of Rob and ComPct, how would I go about that?



Thanks, Excel 2010 btw.

[vlady]

try the index match function.

indexmatch.xlsx

[Tom_J_W]

This is great! Thank you

[vlady]

Your very much welcome, have a nice day and good luck on your work.

Regards,
Vladimir

[Tom_J_W]

If I wanted to add an additional condition like Region = north to it is that possible?

[vlady]

you could add a concatenate for two index match

=index(match for first option) & index(match for second option)
the & sign will "join the two"
is this what you want??

[Tom_J_W]

Ah, I don't think so, I'm not entirely sure what the forumla you described would do, I couldn't get it to work myself.

If you take a look at the attached I would be looking for the requirements 'Agent=Tom, Type=Inbound, Column = Sales.indexmatch.xlsx

[JosephP]

perhaps

=LOOKUP(2,1/(Table1[Agent]="Tom")/(Table1[Type]="Inbound"),Table1[Sales])

[vlady]

look below.

indexmatch rev.xlsx