Vlookup for dynamically changing rows

oz67

New Member
Joined
Aug 3, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hi,
I am trying to do a vlookup a value, but the table_array changes as more data added. How can the table_array updates automatically?

Here is my formula: VLOOKUP(N2514,'On Call Contracts'!$H$13673:$H$14935,1,FALSE). When I add more data to "On Call Contracts" sheet, the range changes. Is there a way to make the range dynamic that updates automatically instead of manually changing the range? The $H$13673:$H$14935 is a date range, like FY21 03. When I add more data, the range can change.

Thanks,
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,707
Office Version
  1. 2016
Platform
  1. Windows
Welcome to the Forum,

One way is to have the data in a Data Table the VLookup would reference the Table. Another option would be to use a named range and the range would use a formula such as

=Sheet1!$A$1:INDEX(Sheet1!$C:$C,MAX((Sheet1!$C:$C<>"")*(ROW(Sheet1!$C:$C))))
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,951
Office Version
  1. 365
Platform
  1. Windows
The formula that you show us serves no real purpose, unless there is more to it, =N2514 would suffice.

There are many ways to make your table dynamic, if there is nothing else below it then you could use

=VLOOKUP(N2154,'On Call Contracts'!$H$13673:INDEX('On Call Contracts'!$H:$H,MATCH("zzz",'On Call Contracts'!$H:$H)),1,0)
 

oz67

New Member
Joined
Aug 3, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Thanks for the reply. I attached 2 screenshots.
The value N2514 is a serial number in Sheet1 (Capture.png).
The table array 'On Call Contracts'!$H$13673:$H$14935 is in sheet "On Call Contracts" (Capture2.png). I have numerous Periods (col A) and each period has a certain range. This specific range has FY21 03. When I add data to this sheet every month, the range of each period changes and I have to manually fix the ranges in the vlookup in Sheet1.
As you notice in capture2, the row H14936 becomes FY21 04. I only need to capture the value N2514 in FY21 03. When I add more data to FY21 03 the range changes.

Hope I explained it better.

Thanks,
 

Attachments

  • Capture.PNG
    Capture.PNG
    88.4 KB · Views: 4
  • Capture2.PNG
    Capture2.PNG
    96.8 KB · Views: 4

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,951
Office Version
  1. 365
Platform
  1. Windows
Your formula is only looking up and returning the exact same value that is in N2514.

N2514 contains PM13327
Your VLOOKUP formula will only ever return PM13327, this makes the lookup function pointless unless you are looking for errors, but there is no indication of this anywhere.

Your question doesn't tell us what you want to do it tells us how you 'think' you need to do it, which doesn't help us, or you.

If you want a solution instead of just an answer then you need to tell us what you want as the end result of the formula, not how you think you need to solve the problem.

If you need to validate that the contract number is found in the specified period then use this formula which will return the contract number if a valid match is found in the specified period, if no match is found then it will return "Yes"

=IF(COUNTIFS('On Call Contracts'!$A:$A,A2514,'On Call Contracts'!$H:$H,N2514),N2514,"Yes")

If that is not what you want then a better description of the expected end result is needed.
 

oz67

New Member
Joined
Aug 3, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
This actually solves the problem. Thanks a lot!
 

Forum statistics

Threads
1,144,524
Messages
5,724,839
Members
422,583
Latest member
neomilan

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
Top