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

Cooki

Board Regular
Joined
Jul 31, 2018
Messages
75
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,846
The formula
=Partner Finance
will return the intersection of the two named ranges Partner and Finance.
The space is Excel's intersection operator.
 

Cooki

Board Regular
Joined
Jul 31, 2018
Messages
75
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?
 

Cooki

Board Regular
Joined
Jul 31, 2018
Messages
75
Works perfect thank you very much

Been at this for a day or so.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,877
Messages
5,574,770
Members
412,617
Latest member
mlharris
Top