How to lookup a value within a specific range within an array

[gguevara]

Hello,

I am trying to lookup a value that resides within a specific range of cells (or Index/Match). This range is dynamic and changes in size. The beginning of this specific range starts with a known cell value and ends with another known cell value. The value I am trying to lookup is somewhere inside this specific dynamic range. However, this specific dynamic range also resides inside a larger overall array range that is also dynamic an changes in size. The size of this larger overall array range does not really matter, as long as I can find the smaller specific dynamic range within, and then lookup the value within.

The logic would be as follows:
Look in overall array A:A, and find a specific range that being with a cell containing "16S:CADETL" and ends with a cell containing "16R:CADETL". Then find and lookup cell value "A1" in order to return "YES".

I want only the "A1" within the specific range. There can be many other "A1" values within the larger overall array range, however I only want the "A1" within the smaller specific range. In other words, the "A1" can exist multiple times within the overall array range (or not), but I want only the specific "A1" within the specific range.

Here is an illustration of what I want as a result:

Excel 2010

	A	B	C	D
Data Codes	Values
not this one
not this one
not this one

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]16S:LINK[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]X[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]X[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]A1[/TD]
[TD="align: center"]NO[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]X[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]A1[/TD]
[TD="align: center"]NO[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]X[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]X[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]16R:LINK[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FFFF00, align: center"]16S:CADETL[/TD]
[TD="bgcolor: #FFFF00, align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FFFF00, align: center"]9[/TD]
[TD="bgcolor: #FFFF00, align: center"]X[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FFFF00, align: center"]11[/TD]
[TD="bgcolor: #FFFF00, align: center"]X[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #00B050, align: center"]A1[/TD]
[TD="bgcolor: #00B050, align: center"]YES[/TD]
[TD="bgcolor: #00B050"]I want this one[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FFFF00, align: center"]14[/TD]
[TD="bgcolor: #FFFF00, align: center"]X[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FFFF00, align: center"]16R:CADETL[/TD]
[TD="bgcolor: #FFFF00, align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]16S:OPTN[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]X[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]X[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]X[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: center"]A1[/TD]
[TD="align: center"]NO[/TD]

[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]X[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: center"]16R:OPTN[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

Any help would be greatly appreciated.

Thanks,
ggee

 

[gguevara]

Hello Aladin,

Actually... I was able to take your formula and rearrange it so that it looks up the original vertical raw data on sheet1. In this way I do not need to create another table in sheet2 (it is not needed).

=VLOOKUP("A1",OFFSET(INDEX(A:A,MATCH("16S:CADETL",A:A,0),0),0,0,MATCH("16R:CADETL",A:A,0)-MATCH("16S:CADETL",A:A,0)+1,2),2,FALSE)

This formula uses OFFSET in the more familiar A1 format. Whereas Hans' formula uses INDIRECT in the less familiar R1C1 format.

Thanks to both of you. Please keep an eye on this thread, as I have one more piece to work out that is related to this.
ggee

 

[Aladin Akyurek]

Hello Aladin,

Actually... I was able to take your formula and rearrange it so that it looks up the original vertical raw data on sheet1. In this way I do not need to create another table in sheet2 (it is not needed).

=VLOOKUP("A1",OFFSET(INDEX(A:A,MATCH("16S:CADETL",A:A,0),0),0,0,MATCH("16R:CADETL",A:A,0)-MATCH("16S:CADETL",A:A,0)+1,2),2,FALSE)

This formula uses OFFSET in the more familiar A1 format. Whereas Hans' formula uses INDIRECT in the less familiar R1C1 format.

Thanks to both of you. Please keep an eye on this thread, as I have one more piece to work out that is related to this.
ggee

If you want to hard code the look up specifications directly in the formula, it's okay with me. But all this has does not effect any changing to the vertical data in Sheet1.

=VLOOKUP("A1",OFFSET(INDEX(Sheet1!A:A,MATCH("16S:CADETL",Sheet1!A:A,0)),0,0,MATCH("16R:CADETL",Sheet1!A:A,0)-MATCH("16S:CADETL",Sheet1!A:A,0)+1,2),2,0)

No need to modify the 0 parameter of VLOOKUP to FALSE at all.

 

[gguevara]

I need to pin point a reference cell and then define the range it is in, then vlookup inside this range

Hello,

If you are familiar with SWIFT messages (ISO-15022) you might recognize some of the text-fields in column-A (MT564 messages to be exact).

Anyways, I need a formula or set of formulas that can do the following:
First I need to find a specific text-field within column-A ("DFLT//Y"). There are similar text-fields that can exist multiple times in the overall "big-picture" range ("DFLT//N"). However, the specific "DFLT//Y" can only exist once. I need to pinpoint this "DFLT//Y" as the reference cell (this is the driving factor of what I am ultimately trying to accomplish - see below).

This "DFLT//Y" resides within a dynamic range in column-A. This dynamic range starts with 16S:CAOPTN, and ends with 16R:CAOPTN (note the difference - 16S vs. 16R). As you can see, there are multiple instances of these 16S:CAOPTN/16R:CAOPTN ranges, and each is dynamic (they can be of any size and any multiples). I have colored each range in column-A for easy illustration. So first I need to find the only "DFLT//Y" that exists, in order to determine and target the specific dynamic range it resides in (this is the second driving factor).

Once I define this specific range, I want to be able to vlookup only inside this specific range. The test is to find the lucky "A1//Y" in column-A and return the "YES" value from column-B. I have colored the vlookup range in column-B in yellow for easy illustration. You can see that it is nested besides the related green colored range.

So in other words (to put it simply), I need to zoom-in and pinpoint the "DFLT//Y" first. Then zoom-out and define it's specific range. Then stay within this specific range and scan for "A1//Y" and return the YES value.

Please be aware that the overall "big-picture" range in column-A can be of any size (dynamic). Sometimes its small, and other times its very large. Likewise, the specific ranges are also dynamic and can be of any size - as well as repeat any number of multiple times. That is why I need for the formulas to also be dynamic, and not use fixed hard-coded cell references.

Please see a screen shot of the excel example:

Excel 2010
	A	B	C	D
1	Data Codes	Values
2	16S:LINK
3	10//1	1
4	A1//Y	Y
5	17//X	X
6	16R:LINK
7	16S:CAOPTN			Comments:
8	CAON//1	1
9	DFLT//N	N		This is the 1st dynamic range of 16S:CAOPTN & 16R:CAOPTN pairs.
10	A1//N	N		I don't want range because it has a "DFLT//N" inside.
11	14//X	X
12	16R:CAOPTN
13	16S:CAOPTN
14	CAON//2	2		This is the 2nd dynamic range of 16S:CAOPTN & 16R:CAOPTN pairs.
15	DFLT//Y	Y		I want this target range because it has a "DFLT//Y" inside.
16	A1//Y	YES		Note: there can only ever be one instance of a DFLT//Y,
17	10//X	X		which is why I want this specific range.
18	14//X	X		Then I want to vlookup inside this range only for "A1//Y"
19	17//X	X		and return the "YES" value.
20	16R:CAOPTN
21	16S:CAOPTN
22	CAON//3	3
23	DFLT//N	N		This is the 1st dynamic range of 16S:CAOPTN & 16R:CAOPTN pairs.
24	A1//Y	N		I don't want range because it has a "DFLT//N" inside.
25	16R:CAOPTN
26	16S:CADETL
27	9//X	X
28	11//X	X
29	A1//N	N
30	14//X	X
31	16R:CADETL
32
Sheet1

I hope my explanation makes sense, and I hope my excel example with colors helps to illustrate what I am looking to do. I have been trying for days and nights. My head is exploding. I asked a very similar question in another post, but I do not think I explained myself correctly. So I better organized my excel example and colored illustration.

Any help will be greatly appreciated.
Thanks,
ggee

 

[Peter_SSs]

Re: I need to pin point a reference cell and then define the range it is in, then vlookup inside this range

I don't supposed that we would be lucky enough that ..

a) The section in question always contains "A1//Y" in col A and
b) The "A1//Y" always came below the "DFLT//Y"?

If so, you could try the formula in D1

Otherwise, try the D2 formula.

Extend the ranges beyond row 100 to whatever is the largest you would ever need.

Excel 2010 32 bit
	A	B	C	D
1	Data Codes			YES
2	16S:LINK			YES
3	10//1	1
4	A1//Y	Y
5	17//X	X
6	16R:LINK
7	16S:CAOPTN
8	CAON//1	1
9	DFLT//N	N
10	A1//N	N
11	14//X	X
12	16R:CAOPTN
13	16S:CAOPTN
14	CAON//2	2
15	DFLT//Y	Y
16	A1//Y	YES
17	10//X	X
18	14//X	X
19	17//X	X
20	16R:CAOPTN
21	16S:CAOPTN
22	CAON//3	3
23	DFLT//N	N
24	A1//Y	N
25	16R:CAOPTN
26	16S:CADETL
27	9//X	X
28	11//X	X
29	A1//N	N
30	14//X	X
31	16R:CADETL
32
Lookup

Cell Formulas
Range	Formula
D1	=VLOOKUP("A1//Y",INDEX(A1:A100,MATCH("DFLT//Y",A1:A100,0)):B100,2,0)
D2	=VLOOKUP("A1//Y",INDEX(A1:A100,AGGREGATE(14,6,(ROW(A1:A100)-ROW(A1)+1)/((A1:A100="16S:CAOPTN")*(ROW(A1:A100)"DFLT//Y",A1:A100,0))),1)):INDEX(B1:B100,AGGREGATE(15,6,(ROW(A1:A100)-ROW(A1)+1)/((A1:A100="16R:CAOPTN")*(ROW(A1:A100)>MATCH("DFLT//Y",A1:A100,0))),1)),2,0)

 

[gguevara]

Re: I need to pin point a reference cell and then define the range it is in, then vlookup inside this range

I don't supposed that we would be lucky enough that ..

a) The section in question always contains "A1//Y" in col A and
b) The "A1//Y" always came below the "DFLT//Y"?

Otherwise, try the D2 formula.

Extend the ranges beyond row 100 to whatever is the largest you would ever need.

Hello Peter,

Thank you for the quick reply.
To answer your questions above:
a) Yes, the "A1//Y" will always be in column-A. In fact, any search cell will be in column-A.
b)No, the "A1//Y" will not always be directly below the "DFLT//Y". It can be several cells below, or it can be several cell above the "DFLT//Y". But the "A1//Y" will never be outside the specific "16S:CAOPTN - DFLT//Y - 16R:CAOPTN" range (no matter what size this range is).

Your second formula in cell D2 seems to be doing the trick. I have tested it by placing the "DFLT//Y" into one of the other ranges, and then placing the "A1//Y" above or below it, but staying within that range's boundary. It is working.

I am trying to understand the formula by looking at each part of the formula (the formulas inside the formulas).
Can you please give an brief explanation of the formula? This way I can better understand it and be able to troubleshoot it or alter it in case of an issue or slight variable.

Thanks,
ggee

 

[Macropod]

gguevara,
With reference to your other thread on this issue (https://www.mrexcel.com/forum/excel...ge-then-vlookup-inside-range.html#post4746936), please don't post what is essentially the same question in multiple threads. (See Forum Rule #12 http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html & Forum Guidelines 6 & 7 https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html)

As both threads have replies, I will now merge them. This may lead to some overlap, but that's a small price to pay...

 

[Aladin Akyurek]

gguevara,
With reference to your other thread on this issue (https://www.mrexcel.com/forum/excel...ge-then-vlookup-inside-range.html#post4746936), please don't post what is essentially the same question in multiple threads. (See Forum Rule #12 http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html & Forum Guidelines 6 & 7 https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html)

As both threads have replies, I will now merge them. This may lead to some overlap, but that's a small price to pay...

Merging has dropped the last question of OP and my reply along with it...

The question was:

Today, 06:00 PM #14
gguevara View Profile
View Forum Posts
Private Message
Add as Contact
New Member Join DateSep 2014Posts9
Re: How to lookup a value within a specific range within an array
Hello again,

Once again, thank you for helping me with the first formula. Essentially this first formula allows me to define the specific range within a larger overall range, and then vlookup a cell value within this specific range.

I have a second case that is similar, but the logic is somewhat in reverse (sort of):
First I need to find a specific cell-text with a specific value. This cell-text can exist multiple times in the larger overall range. However the specific value can only exist once. Therefore, this cell-text/value combination will only exist once - cell-text DFLT with a value of Y (this is the driving factor).

Once I find this DFLT=Y combination, I then want to define the specific range that it lives in. It lives within one of the 16S:CAOPTN/16R:CAOPTN pair ranges. As you can see, there are multiple 16S:CAOPTN/16R:CAOPTN pair ranges. But the specific DFLT=Y combination only lives inside one of these pair ranges. I have been trying to pin point the DFLT=Y reference, then use OFFSET in an upward direction until it intersects with 16S:CAOPTN and downward until it intersects with 16R:CAOPTN. This would be the start and end of the specific range.

Once I define this specific range (that houses the specific DFLT=Y combination), I want to be able to vlookup inside this specific range and find my lucky "A1" and return the YES value.

In other words (putting it simply), I need to zoom-in and pinpoint the DFLT=Y first. Then zoom-out and define it's range residence. Then stay within this range and scan for "A1" and return YES.

Please see a screen shot of the excel example: [...]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][/tr][tr][td]

1​

[/td][td]Data Codes[/td][td]Values[/td][/tr]
[tr][td]

2​

[/td][td]16S:LINK[/td][td][/td][/tr]
[tr][td]

3​

[/td][td]

10​

[/td][td]X[/td][/tr]
[tr][td]

4​

[/td][td]A1[/td][td]NO[/td][/tr]
[tr][td]

5​

[/td][td]

17​

[/td][td]X[/td][/tr]
[tr][td]

6​

[/td][td]16R:LINK[/td][td][/td][/tr]
[tr][td]

7​

[/td][td]16S:CAOPTN[/td][td][/td][/tr]
[tr][td]

8​

[/td][td]CAON[/td][td]

1​

[/td][/tr]
[tr][td]

9​

[/td][td]DFLT[/td][td]N[/td][/tr]
[tr][td]

10​

[/td][td]A1[/td][td]NO[/td][/tr]
[tr][td]

11​

[/td][td]

14​

[/td][td]X[/td][/tr]
[tr][td]

12​

[/td][td]16R:CAOPTN[/td][td][/td][/tr]
[tr][td]

13​

[/td][td]16S:CAOPTN[/td][td][/td][/tr]
[tr][td]

14​

[/td][td]CAON[/td][td]

2​

[/td][/tr]
[tr][td]

15​

[/td][td]DFLT[/td][td]Y[/td][/tr]
[tr][td]

16​

[/td][td]A1[/td][td]YES[/td][/tr]
[tr][td]

17​

[/td][td]

10​

[/td][td]X[/td][/tr]
[tr][td]

18​

[/td][td]

13​

[/td][td]X[/td][/tr]
[tr][td]

19​

[/td][td]

17​

[/td][td]X[/td][/tr]
[tr][td]

20​

[/td][td]16R:CAOPTN[/td][td][/td][/tr]
[tr][td]

21​

[/td][td]16S:CAOPTN[/td][td][/td][/tr]
[tr][td]

22​

[/td][td]CAON[/td][td]

3​

[/td][/tr]
[tr][td]

23​

[/td][td]DFLT[/td][td]N[/td][/tr]
[tr][td]

24​

[/td][td]A1[/td][td]NO[/td][/tr]
[tr][td]

25​

[/td][td]16R:CAOPTN[/td][td][/td][/tr]
[tr][td]

26​

[/td][td]16S:CADETL[/td][td][/td][/tr]
[tr][td]

27​

[/td][td]

9​

[/td][td]X[/td][/tr]
[tr][td]

28​

[/td][td]

11​

[/td][td]X[/td][/tr]
[tr][td]

29​

[/td][td]A1[/td][td]NO[/td][/tr]
[tr][td]

30​

[/td][td]

14​

[/td][td]X[/td][/tr]
[tr][td]

31​

[/td][td]16R:CADETL[/td][td][/td][/tr]
[/table]

My single formula answer (in line with OP's desire) was:

=VLOOKUP("A1",OFFSET($A$2,MAX(IF(MATCH(1,IF($A$2:$A$31="DFLT",IF($B$2:$B$31="Y",1)),0)>IF($A$2:$A$31="16S:CAOPTN",ROW($A$2:$A$31)-ROW($A$2)+1),ROW($A$2:$A$31)-ROW($A$2)+1)),0,MIN(IF($A$2:$A$31="16R:CAOPTN",IF(ROW($A$2:$A$31)-ROW($A$2)+1>MATCH(1,IF($A$2:$A$31="DFLT",IF($B$2:$B$31="Y",1)),0),ROW($A$2:$A$31)-ROW($A$2)+1)))-MAX(IF(MATCH(1,IF($A$2:$A$31="DFLT",IF($B$2:$B$31="Y",1)),0)>IF($A$2:$A$31="16S:CAOPTN",ROW($A$2:$A$31)-ROW($A$2)+1),ROW($A$2:$A$31)-ROW($A$2)+1)),2),2,0)

 

[Macropod]

Merging has dropped the last question of OP and my reply along with it...

Curious. I wonder why it did that?! Anyway, I'm glad you kept a record.

 

[Peter_SSs]

Re: I need to pin point a reference cell and then define the range it is in, then vlookup inside this range

Can you please give an brief explanation of the formula?

The first AGGREGATE finds the largest row in the column A range where both the following conditions are true:
- The cell contains "16S:CAOPTN"
- The row is above (ie < ) the "DFLT//Y" row

For the post #14 layout that returns 13

The second AGGREGATE finds the smallest row in the column A range where both the following conditions are true:
- The cell contains "16R:CAOPTN"
- The row is below (ie > ) the "DFLT//Y" row

For the post #14 layout that returns 20

So the formula becomes
=VLOOKUP("A1//Y",INDEX(A1:A100,13):INDEX(B1:B100,20),2,0)
and therefore
=VLOOKUP("A1//Y",A13:B20,2,0)

 

[Peter_SSs]

One further point.
IF "A1//Y" always exists in the "DFLT//Y" range in question (A13:A20 in the post #14 example) the the formula can be reduced to:

=VLOOKUP("A1//Y",INDEX(A1:A100,AGGREGATE(14,6,(ROW(A1:A100)-ROW(A1)+1)/((A1:A100="16S:CAOPTN")*(ROW(A1:A100)<MATCH("DFLT//Y",A1:A100,0))),1)):B100,2,0)