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]

Ok Aladin and Markmzz everything works great. There's a small glitch when one of the rows contains data like 296-320 or 906-929. How do I handle that?

 

[markmzz]

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

You're welcome and thanks for the feedback.

Markmzz

 

[markmzz]

Ok Aladin and Markmzz everything works great. There's a small glitch when one of the rows contains data like 296-320 or 906-929. How do I handle that?

Vivamar,

In this new case, try this:

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

Markmzz

 

[Aladin Akyurek]

Ok Aladin and Markmzz everything works great. There's a small glitch when one of the rows contains data like 296-320 or 906-929. How do I handle that?

What I have suggested in the last post should work unmodified:

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

 

[vivamar]

I am trying it with a sample range like the one below. Am I missing something?

926-2000
906-926
872-905
852-871
822-851
799-821

 

[Aladin Akyurek]

I am trying it with a sample range like the one below. Am I missing something?

926-2000
906-926
872-905
852-871
822-851
799-821

This list seems to ask for the original formula I posted... Would you please check?

 =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))</SPAN>

 

[markmzz]

I am trying it with a sample range like the one below. Am I missing something?

926-2000
906-926
872-905
852-871
822-851
799-821

Hi Vivamar,

Did you try my formula of the post #23 (the formula below)?

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

If yes, what you got?

Could you post the new data that you are testing with the formula (ranges - A1:A20, B1:B20, A5, D1 and tt)?


Markmzz

 

[vivamar]

Thanks guys for your patience!

Here's how the data changes for the new test. I am talking a short sample for brevity

A1:A20 goes from 1,2,3,4,5,6 and so on till 20
B1:B20 goes like

926-2000
906-926
872-905
852-871
822-851
799-821
and so on..

A5 is irrelevant as I use it to select excel sheets and that's working

D1 contains 920

tt remains same

When I use Aladin's formula, I get Value Not Available Error

When I use markmzz formula I get the last row data i.e. 20 for anything I enter

Expected Result

Since I entered 920, I should get 2 back as 920 falls in 906-926

 

[Aladin Akyurek]

Thanks guys for your patience!

Here's how the data changes for the new test. I am talking a short sample for brevity

A1:A20 goes from 1,2,3,4,5,6 and so on till 20
B1:B20 goes like

926-2000
906-926
872-905
852-871
822-851
799-821
and so on..

A5 is irrelevant as I use it to select excel sheets and that's working

D1 contains 920

tt remains same

When I use Aladin's formula, I get Value Not Available Error

When I use markmzz formula I get the last row data i.e. 20 for anything I enter

Expected Result

Since I entered 920, I should get 2 back as 920 falls in 906-926

You need to sort/put those values in ascending order, not descending as you have.

 

[vivamar]

You need to sort/put those values in ascending order, not descending as you have.

Thanks for responding. Unfortunately I cannot do that as this is the only column that has data in a descending order, rest all others are ascending and the order cannot be changed in the excel sheet. Can the formula be edited in someway or any alternate solution only for this particular column?

Looking forward to your answer.