make "lookup array" and "return array" based on table headings

[smking204]

here's the formula I'm working with:

=XLOOKUP(A13,BC_Items!$A:$A,BC_Items_31!$B:$B,"")

I want the "lookup array" and "return array" to follow the headings, since the column addresses are subject to change.

I used to accomplish this by using vlookup and using MATCH() to determine the column index. Hoping I can migrate this functionality over to xlookup somehow.

Thanks so much!!

 

[Jeffrey Mahoney]

There is nothing wrong with using VLOOKUP. I read an article that MS optimized a bunch of functions to improve speed including VLOOKUP.

If you insert or delete columns in the BC_Items table, your XLOOKUP formula will adjust automatically.

It's puzzling how you are using columns from 2 different tables in the XLOOKUP function. Is that data identical?

What are your column headers?

You can use the combination of Index / Match to get a column with a header match, like below

Book4
	A	B	C	D	E
1	Header1	Header2	Header3		Header2
2		1			1
3		2			2
4		3			3
5					0
6					0
Sheet1

Cell Formulas
Range	Formula
E:E	E1	=INDEX(A:C,,MATCH("Header2",A1:C1,0))
Dynamic array formulas.