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

 

[Aladin Akyurek]

So we are looking for text "A1" ic column A of Sheet1 that is between a pair of 16S:CADETL and 16S:CADETL and if successful return the corresponding value fro column B of Sheet1. Right?

 

[gguevara]

Hello,

Yes this is what I am trying to do.
However, please notice that the pair of 16S:CADETL text are not the same. One is 16S (this is the start of the pair), and the other is 16R (this is the end of the pair). Aside from this technicality, you are correct in your assumption of what I am trying to accomplish.

Thanks,
ggee

 

[Hans K]

For example

=VLOOKUP("A1",INDIRECT("R"&MATCH("16S:CADETL",B2:B23,0)+1&"C2:R"&MATCH("16R:CADETL",B2:B23,0)+1&"C3",0),2,0)

 

[gguevara]

For example

=VLOOKUP("A1",INDIRECT("R"&MATCH("16S:CADETL",B2:B23,0)+1&"C2:R"&MATCH("16R:CADETL",B2:B23,0)+1&"C3",0),2,0)

Hello Hans,

This formula does not work. I get an #N/A error. I am inputting this formula into cell C14 (next to the YES value I want to return).
I see what you are trying to do by using the INDIRECT function, but I am not too familiar with this function enough to troubleshoot it and why it is giving me an error.

Thanks,
ggee

 

[Hans K]

It works fine for me.

https://www.dropbox.com/s/ourrcbtbqdwvg2e/RangeInArray.xlsx?dl=0

 

[Aladin Akyurek]

Hello,

Yes this is what I am trying to do.
However, please notice that the pair of 16S:CADETL text are not the same. One is 16S (this is the start of the pair), and the other is 16R (this is the end of the pair). Aside from this technicality, you are correct in your assumption of what I am trying to accomplish.

Thanks,
ggee

Let A:B of Sheet1 house the data (the table).

Sheet2

[TABLE="class: grid, width: 309"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16S:CADETL[/TD]
[TD]16R:CADETL[/TD]
[TD]A1[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In D2 enter:

=VLOOKUP(C2,OFFSET(INDEX(Sheet1!A:A,MATCH($A2,Sheet1!A:A,0)),0,0,MATCH($B2,Sheet1!A:A,0)-MATCH($A2,Sheet1!A:A,0)+1,2),2,0)

 

[gguevara]

It works fine for me.

https://www.dropbox.com/s/ourrcbtbqdwvg2e/RangeInArray.xlsx?dl=0

Hello Hans,

I down loaded your version. I see why my does not work. My original only has 2 columns of data. The example I pasted on this post as an HTML added a third column (the row numbers). Where my example had columns A and B, your example has columns A, B and C.

Also, I see that your formula recreates the cell references in R1C1 format, which is then used as an Indirect function within the vlookup. Can this be done using A1 format?

Thanks,
ggee

 

[Aladin Akyurek]

See also post #7...

 

[gguevara]

See also post #7...

Hello Aladin,

Yes, I saw your post #7. However, your solution requires me to create another table in sheet2. This means I would be manually rearranging the data into a more horizontal layout in order to do the lookup. However, the raw data is vertical - and I need to lookup in this way.

Thanks for your suggestion. I appreciate it.
ggee