#N/A Error on complicated Index/Match

[bigmyk2k]

(See this forum for the first steps, and what has already been tried)

I am trying to do a complicated Index function where I am doing an INDIRECT(VLOOKUP(... for the Array, a MATCH for the Row Number, and (I think) a MATCH for the Column.

=INDEX(INDIRECT(VLOOKUP($D$2,Section,2,FALSE)),MATCH($B$10,Duration,0),Match($B$9,...?)

Where-

$D$2 refers to the known (user input) section number, in this case: 5
$B$10 refers to the known period of duration (user input), in this case: 24-HR
$B$9 refers to the recorded precip. value (user input), in this case: 1.72​

The Indirect Lookup is finding an array based on a cell which refers to a named list ("Section").
This is in place because the data is identified by the first column of each named array which includes the section number of the data. The "Section" array is:

Section Index Array 1 Sec_1 A17:N31 2 Sec_2 A32:N46 3 Sec_3 A47:N61 4 Sec_4 A62:N76 5 Sec_5 A77:N91 6 Sec_6 A92:N106 7 Sec_7 A107:N121 8 Sec_8 A122:N136 9 Sec_9 A137:N151 <tbody> </tbody>

<tbody>
</tbody>

The Row is found by matching a value found in the column headed as Duration to the value in a named array, also confusingly named, "Duration".

Duration​

10-DAY​

5-DAY​

72-HR​

48-HR​

24-HR​

18-HR​

12-HR​

6-HR​

3-HR​

2-HR​

1-HR​

30-MIN 15-MIN 10-MIN 5-MIN​

<tbody>
</tbody>

What I need to do now is find how to have the function call out the column(s) that bound a lookup value.

For instance, in the example table (below), a reference 24-HR value of 1.72 would be greater than 1.67 (4-MO), and less than 1.93 (6-MO). I know that this is two different lookups, and that is how I want it.

My ultimate goal will be to return all four of those things (1.67, "4-MO", 1.93, "6-MO") in individual cells, but I think finding either the values or the "X-MO" reference will facilitate the other.

Note that the column headers in this table are in A16:N16, whereas the values listed are from the array referenced above ("Sec_5", in A77:N91)

Section	Duration	2-MO	3-MO	4-MO	6-MO	9-MO	1-YR	2-YR	5-YR	10-YR	25-YR	50-YR	100-YR
5	10-DAY	2.2	2.64	3.05	3.58	4.12	4.48	5.2	6.22	7.22	8.61	9.66	10.88
5	5-DAY	1.76	2.11	2.39	2.77	3.18	3.46	4.05	4.94	5.72	6.92	7.98	9.18
5	72-HR	1.51	1.77	2	2.32	2.67	2.9	3.47	4.41	5.16	6.22	7.06	8.12
5	48-HR	1.4	1.64	1.82	2.11	2.43	2.64	3.13	3.93	4.67	5.75	6.52	7.33
5	24-HR	1.31	1.52	1.67	1.93	2.19	2.38	2.91	3.64	4.27	5.15	5.87	6.61
5	18-HR	1.23	1.43	1.57	1.81	2.06	2.24	2.74	3.42	4.01	4.84	5.52	6.21
5	12-HR	1.14	1.32	1.45	1.68	1.9	2.07	2.53	3.17	3.71	4.48	5.11	5.75
5	6-HR	0.98	1.15	1.25	1.45	1.65	1.79	2.18	2.73	3.2	3.86	4.4	4.96
5	3-HR	0.84	0.97	1.06	1.23	1.4	1.52	1.86	2.33	2.73	3.3	3.76	4.23
5	2-HR	0.76	0.88	0.97	1.12	1.27	1.38	1.69	2.11	2.48	2.99	3.4	3.83
5	1-HR	0.62	0.72	0.78	0.91	1.03	1.12	1.37	1.71	2.01	2.42	2.76	3.11
5	30-MIN	0.48	0.56	0.62	0.71	0.81	0.88	1.08	1.35	1.58	1.91	2.17	2.45
5	15-MIN	0.35	0.41	0.45	0.52	0.59	0.64	0.79	0.98	1.15	1.39	1.58	1.78
5	10-MIN	0.28	0.32	0.35	0.41	0.46	0.5	0.61	0.76	0.9	1.08	1.23	1.39
5	5-MIN	0.16	0.19	0.2	0.23	0.27	0.29	0.35	0.44	0.51	0.62	0.7	0.79

<tbody>
</tbody>

 

[Jonmo1]

Is this now an accurate and consistent representation of your 'real' data setup?
Each section is 15 rows, every 15 rows, always every 15 rows?

The last formula I posted in your other thread (Post #16) works just fine for that.
You only needed to change the C10 to B9

But here's another way as well without the need for all the named ranges (except Duration) and indirect/vlookup.

Try this.

=LOOKUP($B$9,OFFSET($C$17,($D$2-1)*15+MATCH($B$10,Duration,0)-1,0,1,12))

 

[Jonmo1]

If you wanted to get the "4-MO" value, we can just add the header row C16:N14 as the last argument of the lookup)

=LOOKUP($B$9,OFFSET(C17,($D$2-1)*15+MATCH($B$10,Duration,0)-1,0,1,12),$C$16:$N$16)

or from the other thread
=LOOKUP($B$9,OFFSET(INDEX(INDIRECT(VLOOKUP($D$2,Section,2,FALSE)),MATCH($B$10,Duration,0),3),,,1,12),$C$16:$N$16)

 

[bigmyk2k]

=LOOKUP($B$9,OFFSET(INDEX(INDIRECT(VLOOKUP($D$2,Section,2,FALSE)),MATCH($B$10,Duration,0),3),,,1,12),$C$16:$N$16)

Eureka!
Now, that's the one!!
Though, I did change the Header reference slightly:
=LOOKUP($B$9,OFFSET(INDEX(INDIRECT(VLOOKUP($D$2,Section,2,FALSE)),MATCH($B$10,Duration,0),3),,,1,12),Table1[[#Headers],[2-MO]:[100-YR]])

Having now worked through the whole thing, I learned a lot about Excel that I thought I already knew.
One more question, how would I then look up the upper bounding value? That is, 1.72 is greater than the 4-MO value, which we looked up, but less than the 6-MO value of 1.93. How do we tweak this to return "6-MO"?

I thought it would be in changing the [Match Type] but that doesn't seem to work.

 

[Jonmo1]

Offset the table headers by 1 column..

=LOOKUP($B$9,OFFSET(INDEX(INDIRECT(VLOOKUP($D$2,Section,2,FALSE)),MATCH($B$10,Duration,0),3),,,1,12),$C$16:$N$16) = "4-MO"
=LOOKUP($B$9,OFFSET(INDEX(INDIRECT(VLOOKUP($D$2,Section,2,FALSE)),MATCH($B$10,Duration,0),3),,,1,12),$D$16:$O$16) = "6-MO"

Or better, no need to do the whole formula twice...
You can then index the header range looking up the result of the first formula, and adding 1..
something like
=INDEX($C$16:$N$16,MATCH($B$11,$C$16:$N$16,0)+1)

Where B11 is the cell holding the formula that returns "4-MO"

 

[bigmyk2k]

Or better, no need to do the whole formula twice...
You can then index the header range looking up the result of the first formula, and adding 1..
something like
=INDEX($C$16:$N$16,MATCH($B$11,$C$16:$N$16,0)+1)

Where B11 is the cell holding the formula that returns "4-MO"

You're making this too easy.

 

[Jonmo1]

Note, that method will error if the result of the first formula is 100-YR
Because there is no column header to the right of that.

You'd have to add an IFERROR
=IFERROR(INDEX($C$16:$N$16,MATCH($B$11,$C$16:$N$16,0)+1),"No Header Value after 100-YR")

 

[bigmyk2k]

Note, that method will error if the result of the first formula is 100-YR
Because there is no column header to the right of that.

You'd have to add an IFERROR
=IFERROR(INDEX($C$16:$N$16,MATCH($B$11,$C$16:$N$16,0)+1),"No Header Value after 100-YR")

Yeah, and also if a value is less than "2-MO". I did see that one coming, and fortunately I'm good with really unnecessarily complicated IF statements.

 

[Jonmo1]

Yeah, and also if a value is less than "2-MO".

You should add another column to the 'Sections' to include add a column of 0's to the left of the 2-MO column...
And you'd have to change the 12 in that formula to 13.