Using row number within VLOOKUP formula

DarrenK

Board Regular
Joined
Aug 5, 2017
Messages
65
Office Version
  1. 365
Platform
  1. Windows
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:
1649255212691.png

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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
You could do something like this, which automatically picks up the bottom row:

Book1
HMHNHOHPHQHRHSHTHUHVHWHXHYHZIAIBIC
1CST Calls presentedCST calls handled# of reps on the phone# of Call Offs# of Scheduled Off# Abandoned% AbandonedCST Calls presentedCST calls handled# of reps on the phone# of Call Offs# of Scheduled Off# Abandoned% Abandoned
2200117551923888%100908070605050%
31101009080706055%
4200117551923888%
5
Data
Cell Formulas
RangeFormula
HM2:HS2HM2=INDEX(Data!HW:IC,COUNTA(Data!HW:HW),0)
Dynamic array formulas.


This assumes no blank rows in column HW.
 
Upvote 0
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.
 
Upvote 0
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
HJHKHLHMHNHOHPHQHRHSHTHUHVHWHXHYHZIAIBIC
1Start Row# of rowsCST Calls presentedCST calls handled# of reps on the phone# of Call Offs# of Scheduled Off# Abandoned% AbandonedCST Calls presentedCST calls handled# of reps on the phone# of Call Offs# of Scheduled Off# Abandoned% Abandoned
2321101009080706055%100908070605050%
3200117551923888%1101009080706055%
4200117551923888%
5
Data
Cell Formulas
RangeFormula
HM2:HS3HM2=INDEX(Data!HW:IC,SEQUENCE(HK2,,HJ2),{1,2,3,4,5,6,7})
Dynamic array formulas.
 
Upvote 0
Solution
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?
 
Upvote 0
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.
 
Upvote 0
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
HJHKHLHMHNHOHPHQHRHS
1Start Row# of rowsCST Calls presentedCST calls handled# of reps on the phone# of Call Offs# of Scheduled Off# Abandoned% Abandoned
2321101009080706055%
3200117551923888%
4
5
6
7Start Row# of rowsCST Calls presentedCST calls handled# of reps on the phone# of Call Offs# of Scheduled Off# Abandoned% Abandoned
8321101009080706055%
9200117551923888%
10       
11
12Start Row# of rowsCST Calls presented# of reps on the phone# Abandoned% Abandoned
133211090600.55
1420055880.08
15
Data
Cell Formulas
RangeFormula
HM2:HS3HM2=INDEX(Data!HW:IC,SEQUENCE(HK2,,HJ2),{1,2,3,4,5,6,7})
HM8:HS10HM8=IF(ROWS($HM$8:$HM8)>$HK$8,"",INDEX(Data!HW:HW,$HJ$8-1+ROWS($HM$8:$HM8)))
HM13:HP14HM13=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!
 
Upvote 0
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?
 
Upvote 0
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:

Excel Formula:
=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.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top