Offset? Match? Lookup? Something else?

[bigmyk2k]

I am trying to find an indirect reference function.
Example:

I want to autopopulate cell B3 with a lookup value.
That value is found in Array "Data", on a separate sheet.
B3 looks at B1 for a date, finds that date in "Data" and returns the value X Rows down, and Y rows over.
X and Y are found through a VLookup at another array.
​

This seems straightforward, but I can't figure out how to do it.

 

[bigmyk2k]

This function does everything I want it to- that is, it produces all the exact results in every step, but Excel doesn't like it for some unspecified reason.
(Note: Cell and array references aren't exactly as specified in the intro)

=OFFSET(VLOOKUP(Analysis!B5,'Data - RG 3'!$A$12:$A$50000,1),VLOOKUP(Analysis!B10,Offset,2,False),VLOOKUP(Analysis!B10,Offset,3,false))

 

[ukmikeb]

Hi

It appears that the 2nd and 3rd VLOOKUPS refer to a named range "Offset". It is not advisable to used Reserved Words as named ranges.

hth

 

[bigmyk2k]

I agree. Sorry, I had changed that shortly after posting, and didn't update (for some reason I don't have the option to edit my previous posts).

Excel remains cranky.

Currentest version:
=OFFSET(VLOOKUP(Analysis!B5,'Data - RG 3'!$A$12:$A$50000,1),VLOOKUP(Analysis!B10,Dur_Off,2,False),VLOOKUP(Analysis!B10,Dur_Off,3,false))

 

[ukmikeb]

You need to provide a sample of your data using either one of the routines in my signature or one of the file sharing sites (neutralizing any sensitive data).

I think you also need to clarify your objectives since I cannot relate the formula to the OP.

 

[bigmyk2k]

You need to provide a sample of your data

Here you go. This is as simple as I can make it and still hopefully get the point across. The formula showing up in A12 should be in (and populating B8).
Thanks for your help!

Example

*	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Rainfall Analysis	RG Site 1	*	*	*	*	*	*	Duration	Offset Rows	Offset Columns	*	*
2	Start of Precipitation Period	4/12/14 9:00 PM	*	*	*	*	*	*	5-DAY	480	13	*	*
3	End of Precipitation Period	4/14/14 12:30 PM	*	*	*	*	*	*	72-HR	288	12	*	*
4	Total System Precipitation	0.00	*	*	*	*	*	*	48-HR	192	11	*	*
5	Storm Analysis	*	*	*	*	*	*	*	24-HR	96	10	*	*
6	Significant Period Start Time	*	*	*	*	*	*	*	18-HR	72	9	*	*
7	Significant Period Duration	24-HR	*	*	*	*	*	*	12-HR	48	8	*	*
8	Significant Period Total	*	*	*	*	*	*	*	6-HR	24	7	*	*
9	*	*	*	*	*	*	*	*	3-HR	12	6	*	*
10	*	*	*	*	*	*	*	*	2-HR	8	5	*	*
11	*	*	*	*	*	*	*	*	1-HR	4	4	*	*
12	=IF(ISBLANK(B6),"",OFFSET(VLOOKUP(B6,A16:M60,1),VLOOKUP(B7,I2:K13,2,False),VLOOKUP(B7,I2:K13,3,false)))	*	*	*	*	*	*	*	30-MIN	2	3	*	*
13	*	*	*	*	*	*	*	*	15-MIN	1	2	*	*
14	*	*	SUMMATION OF DURATION TOTALS
15	Time	Rainfall (IN)	30-MIN	1-HR	2-HR	3-HR	6-HR	12-HR	18-HR	24-HR	48-HR	72-HR	5-DAY
16	04/12/2014 10:45 PM	0.03	0.03	0.26	0.29	0.29	0.29	0.29	0.29	0.29	0.29	0.29	0.29
17	04/12/2014 11:00 PM	0.02	0.05	0.10	0.28	0.31	0.31	0.31	0.31	0.31	0.31	0.31	0.31
18	04/12/2014 11:15 PM	0.01	0.03	0.06	0.29	0.32	0.32	0.32	0.32	0.32	0.32	0.32	0.32
19	04/12/2014 11:30 PM	0.01	0.02	0.07	0.30	0.33	0.33	0.33	0.33	0.33	0.33	0.33	0.33
20	04/12/2014 11:45 PM	0.02	0.03	0.06	0.32	0.35	0.35	0.35	0.35	0.35	0.35	0.35	0.35
21	04/13/2014 12:00 AM	0.01	0.03	0.05	0.15	0.33	0.36	0.36	0.36	0.36	0.36	0.36	0.36
22	04/13/2014 12:15 AM	0.00	0.01	0.04	0.10	0.33	0.36	0.36	0.36	0.36	0.36	0.36	0.36
23	04/13/2014 12:30 AM	0.02	0.02	0.05	0.12	0.35	0.38	0.38	0.38	0.38	0.38	0.38	0.38
24	04/13/2014 12:45 AM	0.01	0.03	0.04	0.10	0.36	0.39	0.39	0.39	0.39	0.39	0.39	0.39
25	04/13/2014 1:00 AM	0.02	0.03	0.05	0.10	0.20	0.41	0.41	0.41	0.41	0.41	0.41	0.41
26	04/13/2014 1:15 AM	0.00	0.02	0.05	0.09	0.15	0.41	0.41	0.41	0.41	0.41	0.41	0.41
27	04/13/2014 1:30 AM	0.00	0.00	0.03	0.08	0.15	0.41	0.41	0.41	0.41	0.41	0.41	0.41
28	04/13/2014 1:45 AM	0.01	0.01	0.03	0.07	0.13	0.42	0.42	0.42	0.42	0.42	0.42	0.42
29	04/13/2014 2:00 AM	0.00	0.01	0.01	0.06	0.11	0.42	0.42	0.42	0.42	0.42	0.42	0.42
30	04/13/2014 2:15 AM	0.00	0.00	0.01	0.06	0.10	0.42	0.42	0.42	0.42	0.42	0.42	0.42
31	04/13/2014 2:30 AM	0.00	0.00	0.01	0.04	0.09	0.42	0.42	0.42	0.42	0.42	0.42	0.42
32	04/13/2014 2:45 AM	0.00	0.00	0.00	0.03	0.07	0.42	0.42	0.42	0.42	0.42	0.42	0.42
33	04/13/2014 3:00 AM	0.00	0.00	0.00	0.01	0.06	0.39	0.42	0.42	0.42	0.42	0.42	0.42
34	04/13/2014 3:15 AM	0.01	0.01	0.01	0.02	0.07	0.40	0.43	0.43	0.43	0.43	0.43	0.43
35	04/13/2014 3:30 AM	0.00	0.01	0.01	0.02	0.05	0.40	0.43	0.43	0.43	0.43	0.43	0.43
36	04/13/2014 3:45 AM	0.00	0.00	0.01	0.01	0.04	0.40	0.43	0.43	0.43	0.43	0.43	0.43
37	04/13/2014 4:00 AM	0.04	0.04	0.05	0.05	0.06	0.26	0.47	0.47	0.47	0.47	0.47	0.47
38	04/13/2014 4:15 AM	0.01	0.05	0.05	0.06	0.07	0.22	0.48	0.48	0.48	0.48	0.48	0.48

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:168px;"><col style="width:111px;"><col style="width:74px;"><col style="width:74px;"><col style="width:74px;"><col style="width:80px;"><col style="width:49px;"><col style="width:77px;"><col style="width:61px;"><col style="width:47px;"><col style="width:62px;"><col style="width:45px;"><col style="width:63px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B4 =SUMIFS('[20150605_Revised Rainfall Analysis - RHO.xlsx]Data - RG 3'!$B$12:$B$50000,'[20150605_Revised Rainfall Analysis - RHO.xlsx]Data - RG 3'!$A$12:$A$50000,">="&'[20150605_Revised Rainfall Analysis - RHO.xlsx]Analysis'!B3,'[20150605_Revised Rainfall Analysis - RHO.xlsx]Data - RG 3'!$A$12:$A$50000,"<"&'[20150605_Revised Rainfall Analysis - RHO.xlsx]Analysis'!B4) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[ukmikeb]

What do you expect from this part of the formula VLOOKUP(B6,A16:M60,1)

B6 seems to contain asterisk!

For Offset that part of the formula should be giving you an address which you offset by 96 rows and 10 columns.

What address should you be offsetting?

 

[bigmyk2k]

Yes, using Excel Jeanie, it fills in blank cells with an Asterisk.
The point of that is to find a user input date and time (entered in cell B6) in the called out array (A16:M60). Then, the latter parts or the formula are to return the value found the appropriate number of rows down and columns across (found by the next two lookups) from that cell.
Yes, I know that this lookup won't work with nothing entered in B6 (technically, working correctly would return a blank cell), but Excel won't accept the formula at all, saying that there is an error.

 

[ukmikeb]

If the user entered 04/13/2014 4:00 AM would you expect B8 to be populated with the contents of J37 or some other cell?

 

[bigmyk2k]

If the user entered 04/13/2014 4:00 AM would you expect B8 to be populated with the contents of J37 or some other cell?

You're ~75% right.

Working through the formula:
=IF(ISBLANK(B6),"", OFFSET( VLOOKUP(B6,A16:M60,1), VLOOKUP(B7,I2:K13,2,False), VLOOKUP(B7,I2:K13,3,false)))

=IF(ISBLANK(B6),"", OFFSET(Cell A37),VLOOKUP(B7,I2:K13,2,False),VLOOKUP(B7,I2:K13,3,false)))

=IF(ISBLANK(B6),"", OFFSET(Cell A37), VLOOKUP("24-HR",I2:K13,2,False), VLOOKUP(B7,I2:K13,3,false)))

=IF(ISBLANK(B6),"", OFFSET(Cell A37), 96, VLOOKUP(B7,I2:K13,3,false)))

=IF(ISBLANK(B6),"", OFFSET(Cell A37),96, VLOOKUP("24-HR",I2:K13,3,false)))

=IF(ISBLANK(B6),"", OFFSET(Cell A37),96,10)

So, (assuming that 04/13/2014 4:00 AM is entered in B6) I want to find the value in the cell 96 rows down, and 10 columns over from Cell A37, aka- J133. (Which is not pasted in the example, for the sake of space.)

This process all makes sense to me, but Excel doesn't like that formula, and tells me there is an error in it.