Accomodate Range in INDEX, INDIRECT

[vivamar]

My formula reads like the following


=INDEX(INDIRECT("'"&LOOKUP(A5,tt)&"'!A1:A20"),MATCH(D1,INDIRECT("'"&LOOKUP(A5,tt)&"'!B1:B20"),0))


where 'tt' is a table name


B1 to B20 contains data that is both as single numbers as well as ranges

-
-
0
1
2-3
4
5-6
7
8
9-10
11
12-13
14
15
16-17
18
19-20
21
22
23-24
25

<tbody>
</tbody>

The formula works perfectly fine when the number in B1:B20 is a single number, but as soon as there's a range like in B5, B7 and so on, it returns a #NA


How can I change the above formula to accomodate range in cells?

 

[vivamar]

Wow thanks. It gives me an error in the formula.

 

[Aladin Akyurek]

Wow thanks. It gives me an error in the formula.

What is the error?

 

[markmzz]

The table you put up runs from B1 to B21, not to B20. Adjust A1:A20 and B1:B20 to suit if necessary.

Note that I tested with a single range...

=INDEX(
  INDIRECT("'"&LOOKUP(A5,tt)&"'!A1:A20"),
   LOOKUP(9.99999999999999E+307,
    SEARCH("-"D1&"-","-"&INDIRECT("'"&LOOKUP(A5,tt)&"'!B1:B20")&"-"),
    ROW(B1:B20)-ROW(B1)+1))

Does this deliver the good?

Hi Aladin,

Your formula have a problem (in red). You forgot the &.

Markmzz

 

[markmzz]

If I understand correctly what you want, maybe this can helps:

Use Ctrl+Shift+Enter and not only Enter to enter the formula below

=INDEX(INDIRECT("'"&LOOKUP(A5,tt)&"'!A1:A20"),MATCH(D1,1*LEFT(SUBSTITUTE(INDIRECT("'"&LOOKUP(A5,tt)&"'!B1:B20"),"-"," "),2)))

Or use only Enter to enter the formula below

=INDEX(INDIRECT("'"&LOOKUP(A5,tt)&"'!A1:A20"),MATCH(D1,INDEX(1*LEFT(SUBSTITUTE(INDIRECT("'"&LOOKUP(A5,tt)&"'!B1:B20"),"-"," "),2),)))

Markmzz

 

[Aladin Akyurek]

Wow thanks. It gives me an error in the formula.

What is the error?

It appears I skipped an obvious & in the formula:

=INDEX(
  INDIRECT("'"&LOOKUP(A5,tt)&"'!A1:A20"),
   LOOKUP(9.99999999999999E+307,
    SEARCH("-"&D1&"-","-"&INDIRECT("'"&LOOKUP(A5,tt)&"'!B1:B20")&"-"),
    ROW(B1:B20)-ROW(B1)+1))

 

[vivamar]

It appears I skipped an obvious & in the formula:

=INDEX(
  INDIRECT("'"&LOOKUP(A5,tt)&"'!A1:A20"),
   LOOKUP(9.99999999999999E+307,
    SEARCH("-"&D1&"-","-"&INDIRECT("'"&LOOKUP(A5,tt)&"'!B1:B20")&"-"),
    ROW(B1:B20)-ROW(B1)+1))

Awesome. Can you take a minute and explain what's going on here. Thanks

 

[markmzz]

Hi Vivamar,

Try this too:

Use only Enter to enter the formula

=LOOKUP(D1,1*LEFT(SUBSTITUTE(INDIRECT("'"&LOOKUP(A5,tt)&"'!B1:B20"),"-"," "),2),INDIRECT("'"&LOOKUP(A5,tt)&"'!A1:A20"))

Markmzz

 

[Aladin Akyurek]

Awesome. Can you take a minute and explain what's going on here. Thanks

MATCH cannot succeed matching 2 against 2-3, even with wildcarding in view of the data type mismatch.

On the other hand, SEARCH is capable identifying a substring like a numeric 2 or text 2 ('2, for example) in another string like 2-3.

The set up needs to be done with caution for we don't want a match between 2 and 21.

In order to circumvent such mismatches, we put "-" around D1 and the range we look at, recognizing already existing delimiter in 2-3, etc.

When SEARCH is given "-"&D1&"-" to look for in "-"&B1:B20&"-", yields a result array like:

{#VALUE!,#VALUE!,1,#VALUE!,....}

with always a single number or none.

LOOKUP with the big number as the look value picks out that number, correlates that number's position in the array with:

ROW(B1:B20)-ROW(B1)+1

which is an adjusted set of row numbers, i.e., {1;2;3;...} and returns the row number to the INDEX bit.

Since there is a single result at any time:

=LOOKUP(9.99999999999999E+307,
    SEARCH("-"&D1&"-","-"&INDIRECT("'"&LOOKUP(A5,tt)&"'!B1:B20")&"-"),
    INDIRECT("'"&LOOKUP(A5,tt)&"'!A1:A20"))

must also work.

 

[vivamar]

Hi Vivamar,

Try this too:

Use only Enter to enter the formula

=LOOKUP(D1,1*LEFT(SUBSTITUTE(INDIRECT("'"&LOOKUP(A5,tt)&"'!B1:B20"),"-"," "),2),INDIRECT("'"&LOOKUP(A5,tt)&"'!A1:A20"))

Markmzz

Wow this looks real simple and spot on. Thanks mate!

 

[vivamar]

Thanks Aladin for the detailed explanation You must have heard this a lot but you really rock