find address from specific value in named range

[DocM3]

Hello Fellow Excellians,

The past couple of hours i've been trying to find an answer to the following question. Google hasn't been able to help me, either this site. Where i can usually find a helping hand. Therefore the only thing i can think of is ask the question here.

I have a couple of different lists. Next to those lists are values. The lists are named ranges. For specific reasons I can't have the values within the named range.

On another sheet the user can select which named range she wants to show. I need the value next to the shown list to calculate with. How can i do this best?

i figured doing an offset is the best way. But thats where the trouble starts. I can't find the exact address of the listed value from with a named range. If for example i have the following list starting on cell D41:

a
b
c
d

and i want to know the address of value 'a', i get the address being A1. the other time i get A5 back... It simply does not make sence, it should output D41. So i can use the indirect value for the offset start..

I'm probably doing this really wrong here. So please enlighten me if you can.

• Vert. lookup is not possible since the values are not within the named range
• i've been trying various forms of index match querries,
• tried to use address

Thanks in advance!

 

[SirJB7]

Hi, DocM3!

Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you, relieving the contributor of having to build a test file, if necessary. Thank you.

Regards!

 

[NARAYANK991]

Hi ,

I'll tell you what I tried , and you can see if it helps :

1. I defined 3 named ranges as follows :

Forecast : =Sheet1!$K$15
Sales : =Sheet1!$G$13
Variance : =Sheet1!$P$1

2. I put in the names of the above ranges in A3 , A4 and A5 , as follows :

Sales
Forecast
Variance

3. In B3 , I put in the formula : =ADDRESS(ROW(INDIRECT(A3)),COLUMN(INDIRECT(A3)))

It returns $G$13.

Narayan

 

[DocM3]

Hi SirJB7 and Narayank991,

Thanks for your anwsers. I've added a sample file so you can see what i've been doing.

Narayank991, your solution looks exactly at what i've tried as well. The problem is that it wont work when you first want need to find the exact cell you want to work from. (A3) in your example.

Please see my added file. I hope that it makes any sence.

Thanks so far!
DocM3

 

[SirJB7]

Hi, DocM3!

Give a look at the uploaded file. It uses a new named range for columns C:D and it has 2 examples of how to achieve your goal with INDEX/MATCH and VLOOKUP functions.

CarTable: =Data!$C$4:$D$13
You can then redefine (not done):
Car: =DESREF(CarTable;;0) -----> in english: =OFFSET(CarTable,,0)
CarStartBudget: =DESREF(CarTable;;1) -----> in english: =OFFSET(CarTable,,1)

Formula with INDEX/MATCH:
G5: =INDICE(CarTable;COINCIDIR(C5;Car;0);2) -----> in english: =INDEX(CarTable,MATCH(C5,Car,0),2)
Formula with VLOOKUP:
H5: =BUSCARV(C5;CarTable;2;FALSO) -----> in english: =VLOOKUP(C5,CarTable,2,FALSE)

The problem with both of your formulas is that you didn't explicitly used the 3rd MATCH parameter, so it assumed 1, and then you must have the list in ascending order, otherwise #N/A# is returned.

Regards!

 

[DocM3]

Wel thank you SirJB7!

How easy it can sometimes be. I should have come up with that myself! I was thinking way too difficult. Thanks very much. Question solved

 

[SirJB7]

Hi, DocM3!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!