dynamic vlookup?

[thewiseguy]

Hello All,

I am trying to understand how to create a vlookup formula that does not need to be changed every time a new column is added to my Table

Here is my output:

20200000-Ener.v1 - Copy.xlsx
	B	C	D	E
2	Line No	MeasureCode	N/R	Floor Number
3	1	PL3		1
PrescriptiveCalcs

Cell Formulas
Range	Formula
B3	B3	=Input!B4
C3	C3	=VLOOKUP(Input!T4,TableProposed,25,0)
E3	E3	=IF(Input!D4=0,"",Input!D4)

If I add a column to "TableProposed," this Vlookup will no longer work. What can I add to the formula so this is not the case?

Thanks in advance!

 

[jasonb75]

Try using INDEX and MATCH instead, or XLOOKUP if you only need to use it with 365.

=INDEX(TableProposed[xyz],MATCH(Input!T4,TableProposed[abc],0))

=XLOOKUP(Input!T4,TableProposed[abc],TableProposed[xyz],"")

In both cases, [abc] refers to column 1 of the table, [xyz] refers to column 25 of the table in your existing formula.

 

[thewiseguy]

Try using INDEX and MATCH instead, or XLOOKUP if you only need to use it with 365.

=INDEX(TableProposed[xyz],MATCH(Input!T4,TableProposed[abc],0))

=XLOOKUP(Input!T4,TableProposed[abc],TableProposed[xyz],"")

In both cases, [abc] refers to column 1 of the table, [xyz] refers to column 25 of the table in your existing formula.

this is great. i was using the 365 array formulas for something else but this totally changes things for me. thanks for the XLOOKUP function, much appreciated!