Convert text to data reference

[liweihao]

Hi,

Here is my Index and Match formula.

=Index($A$1:$q$997,match(R2,A:A,0)+1,match(R3,ADDRESS(MATCH(R2,A:A,0)+1,9,1)& ":" & ADDRESS(MATCH(R2,A:A,0)+1,17,1),0))

Here is my question.

For this part of the formula,inside 2nd Match statement. After calculation it will look like this, Match(R3,$I$8:$q$8,0), but "$I$8:$q$8" will be text format not data format. How can I convert it to data format?

Thank you very much for your help!

 

[NARAYANK991]

Hi ,

The INDIRECT function does this conversion of an string into a reference. Try this :

=INDEX($A$1:$Q$997,MATCH(R2,A:A,0)+1,MATCH(R3,INDIRECT(ADDRESS(MATCH(R2,A:A,0)+1,9,1)& ":" & ADDRESS(MATCH(R2,A:A,0)+1,17,1)),0))

Narayan

 

[liweihao]

Hi Narayan,

Thank you very much for your help.
Everything looks right, but just do not work.

If you look at the screenshot.
INDIRECT("$I$2:$Q$2") will return a n/a value.
If I remove INDIRECT and quotation, the formula works fine.


Again, Thank you very much for your help!

 

[Somendra Misra]

Hi,

I think it shows a +1 in your formula that is causing the problem.

Regards,

 

[NARAYANK991]

Hi ,

Removing both the INDIRECT and the quotation marks , certainly it should work ; but then it will not be dynamic. The INDIRECT is required to make it dynamic , and when INDIRECT is used , the quotes are required.

The formula you have showed in the uploaded pic is not the same as what either you posted in your initial post , nor what I posted in my earlier post.

As Misra has indicated , the +1 at the end of the formula is a mistake ; the following returns a proper value ; whether it is the correct value is for you to decide.

=MATCH(R3,INDIRECT(ADDRESS(MATCH(R2,A:A,0),9,1)&":"&ADDRESS(MATCH(R2,A:A,0),17,1)),0)+1

where the highlighted +1 is your decision.

With or without this , the above formula will return a result.

Your pic shows the outer MATCH function without a 0 for the third parameter , which you need to decide ; is it required or not ?

Narayan

 

[jeffreyweir]

You can likely also use the INDEX function to do this, or the CHOOSE function. It would help if you would upload a sample file.