Using row number within VLOOKUP formula

[DarrenK]

Hello,

I am uncertain how to go about this as I don't believe the ROW formula can work in the way I'm requesting.

File has 2 sheets: Data and Email.
Data tab has the following:

As each day passes, a new row is added.

In the Email sheet, I have a small table that displays the information from the Data tab. Currently, I am copying and pasting the entire row from Data to Email and I have an "=HW#" (# = row number) formula that pulls the info right into my table. The table then gets copied into an outgoing email for the company. Instead of copying and pasting the entire row from one sheet to another, is it possible to merely type the Row number of the newest data in the Data sheet directly into a cell within the Email sheet and have it VLOOKUP the referenced cell data? Or if not VLOOKUP, would the row number require an INDIRECT or INDEX formula? I'm not sure how to begin on this as I haven't found anything online.

 

[Eric W]

You could do something like this, which automatically picks up the bottom row:

Book1
	HM	HN	HO	HP	HQ	HR	HS	HT	HU	HV	HW	HX	HY	HZ	IA	IB	IC
1	CST Calls presented	CST calls handled	# of reps on the phone	# of Call Offs	# of Scheduled Off	# Abandoned	% Abandoned				CST Calls presented	CST calls handled	# of reps on the phone	# of Call Offs	# of Scheduled Off	# Abandoned	% Abandoned
2	200	117	55	19	23	88	8%				100	90	80	70	60	50	50%
3											110	100	90	80	70	60	55%
4											200	117	55	19	23	88	8%
5
Data

Cell Formulas
Range	Formula
HM2:HS2	HM2	=INDEX(Data!HW:IC,COUNTA(Data!HW:HW),0)
Dynamic array formulas.

This assumes no blank rows in column HW.

 

[DarrenK]

It doesn't appear to work totally because certain columns (that I didn't feel were pertinent) have dates prepopulated so the formula is grabbing the last date/week day in those columns and since we're only in April, it's continually pulling December data. For most columns though, the formula did what it was supposed to do. On occasion I will need to grab 2 rows of data so just pulling the bottom row of data will not always work.

 

[Eric W]

If you have some way to identify the last used row, we can use that instead. Maybe looking down a different column? If you want to try your original idea of putting in the row number, you can try this:

Book1
	HJ	HK	HL	HM	HN	HO	HP	HQ	HR	HS	HT	HU	HV	HW	HX	HY	HZ	IA	IB	IC
1	Start Row	# of rows		CST Calls presented	CST calls handled	# of reps on the phone	# of Call Offs	# of Scheduled Off	# Abandoned	% Abandoned				CST Calls presented	CST calls handled	# of reps on the phone	# of Call Offs	# of Scheduled Off	# Abandoned	% Abandoned
2	3	2		110	100	90	80	70	60	55%				100	90	80	70	60	50	50%
3				200	117	55	19	23	88	8%				110	100	90	80	70	60	55%
4														200	117	55	19	23	88	8%
5
Data

Cell Formulas
Range	Formula
HM2:HS3	HM2	=INDEX(Data!HW:IC,SEQUENCE(HK2,,HJ2),{1,2,3,4,5,6,7})
Dynamic array formulas.

 

[DarrenK]

That did it!! Amazing work, Eric. Thank you so much!!

QQ: I see the numbers in the brackets allow the formula to be added into those columns, counted out from the formula cell. Why can't this formula be extended through the normal means of clicking the bottom right corner of the cell?

 

[DarrenK]

Also...instead of adding extra numbers in the brackets, is there a way to reference a range of columns? I tried with a colon and that didn't seem to work.

 

[Eric W]

In Excel 365 (and now Excel 2021), Microsoft introduced the "SPILL" functionality. Basically, if a formula returns an array of data, it would SPILL into adjacent cells to display it all. In previous versions, only the top left value from the array would be displayed. That required using a different formula in each cell to see the entire array, which is what you're used to. If the SPILL functionality is available, I prefer to use it since it only requires 1 formula instead of many. But the old way still works. Consider:

Book1
	HJ	HK	HL	HM	HN	HO	HP	HQ	HR	HS
1	Start Row	# of rows		CST Calls presented	CST calls handled	# of reps on the phone	# of Call Offs	# of Scheduled Off	# Abandoned	% Abandoned
2	3	2		110	100	90	80	70	60	55%
3				200	117	55	19	23	88	8%
4
5
6
7	Start Row	# of rows		CST Calls presented	CST calls handled	# of reps on the phone	# of Call Offs	# of Scheduled Off	# Abandoned	% Abandoned
8	3	2		110	100	90	80	70	60	55%
9				200	117	55	19	23	88	8%
10
11
12	Start Row	# of rows		CST Calls presented	# of reps on the phone	# Abandoned	% Abandoned
13	3	2		110	90	60	0.55
14				200	55	88	0.08
15
Data

Cell Formulas
Range	Formula
HM2:HS3	HM2	=INDEX(Data!HW:IC,SEQUENCE(HK2,,HJ2),{1,2,3,4,5,6,7})
HM8:HS10	HM8	=IF(ROWS($HM$8:$HM8)>$HK$8,"",INDEX(Data!HW:HW,$HJ$8-1+ROWS($HM$8:$HM8)))
HM13:HP14	HM13	=INDEX(Data!HW:IC,SEQUENCE(HK13,,HJ13),{1,3,6,7})
Dynamic array formulas.

In rows 1:3 is the original SPILL formula. In rows 7:10 is the equivalent in the old way. You'd put the formula in HM8, then you can drag the fill handle to extend it to the rest of the range.

In rows 12:14, it shows how to select only certain columns. Depending on the particular situation, there are other techniques to pick out specific rows/columns.

Hope this helps!

 

[DarrenK]

Thank you for the explanation of the Spill function. I saw I had gotten a #SPILL error trying to paste into the worksheet a few times and was never certain what that meant.

And thanks again for the multiple formulas displaying the different ways to get the same result. Lastly, I actually had 50 columns that I needed to use the formula for and I figured out that I had to adjust the SEQUENCE parameters so that it automatically extended the formula which illustrated your description of Spilling. I LOVED SEEING THAT without having to drag and extend. But then my question: to pull the info from the DATA tab, I had to add the array values one by one {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15...}). Is it possible to go from 1 to 50 within the array without having to type all 50 numbers?

 

[Eric W]

Yes, a #SPILL error means the formula is trying to place some values into a cell that already has data in it.

And you DEFINITELY don't want to use the {1,2,3,4,5,6,7} format with 50 columns! Try this instead:

=INDEX(Data!HW:JT,SEQUENCE(HK2,,HJ2),SEQUENCE(,50))

This is the same basic HM2 formula from before, but I expanded the range to Data!HW:JT, and used SEQUENCE for the column selection. SEQUENCE is a new function too. The basic format is:

=SEQUENCE(rows,columns,start,step)

The start and step values default to 1 if not explicitly defined. So for the first SEQUENCE, where I say which rows to include, it says include 2 rows (HK2), starting at 3 (HJ2). The SEQUENCE I used for the columns to include just has 50 in the columns section, so it starts at 1 and goes to 50.