Vlookup for dynamically changing rows

[oz67]

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,

 

[Trevor G]

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]

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]

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,

 

[jasonb75]

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]

This actually solves the problem. Thanks a lot!