Return cell value based of row Named Range and Column Named Range

[Cooki]

Hi All

I want to return the cross reference of 2 named ranges.

There is a sheet called Front Page where i want to return the result and the information is on a sheet called Commission

Row named range = Partner
Column named range = Finance

Both of the named ranges will be referenced in as cell that has been populated by another formula.

So Partner will be taken from a drop down menu in cell $B$2

and Finance will be taken from cell $C$8

I thought the formula would be something like the below, but return #VALUE so im wrong obviously

=INDIRECT(ADDRESS(Partner,Finance))

=INDIRECT(ADDRESS($B$2,$C$8))

Any help would be very much appreciated

 

[mikerickson]

The formula
=Partner Finance
will return the intersection of the two named ranges Partner and Finance.
The space is Excel's intersection operator.

 

[Cooki]

Perfect thank you

How about instead of saying Partner is put B2 which is where my drop down menu is with all the names of the row rangers Partner, Partner_1, Partner_2

and finance is C8 which is a formula which brings back the names of my column ranges Finance, Finance_1, Finance_2

I did try =B2 C8 which i thought would pick the named ranges from those cells, but does not work.

Is there a way?

 

[mikerickson]

You'd need two range objects. Try

=INDIRECT(B2) INDIRECT(C8)

 

[Cooki]

Works perfect thank you very much

Been at this for a day or so.