Index and Match Formula

[BT63]

I need some assistance in writing an index and match formula with an indirect reference. Here's what I have. I have a sheet named "Inventory" that contains 12 identical tables. Each table obviously has a different name. So, for example one is named "MAug23". Then I have a sheet, in the same workbook, named "InventoryReport". I need a formula that will pull the data from the "Inventory" sheet for a particular column. Here is the VLookup formula, but won't work as the data from B7 is in the 2nd column in the table. =VLOOKUP($B7,INDIRECT($D$3),13,0) I am using the Indirect reference in D3 to select the desired table to pull the data from. Hopefully, this makes sense. Any help is much appreciated!!

 

[awoohaw]

I am confused with the title of your post and the formula you have inside it.
Are you matching the second column of each of the 12 tables?
Are the values in the 2nd column repeated in all 12 tables, in the same sequence?
(If not how are you making sure you aren't looking up a value not in the table).

I'm thinking index match is a better process since youre starting in a column different from number 1.

Mr. Excel has a great tool that will probably get you quicker responses to your questions.
It is XL2BB add in (Link below). Using this means the forum will not have to recreate your scenario from scratch... which can lead to errors because of misunderstanding and typos. Help the forum help you.

If you cannot use the add in, please post a table of your values with some good labeling.
Providing an example with the desired solution would be a huge help.

Only in the worst case should you post an image (except for charts and other things that don't get copied with the xl2bb add in ).

Best wishes.

 

[BT63]

Thank you!! I will give the add in a try. I appreciate your help!

 

[Alex Blakenburg]

Do the tables have the same headings for the columns ?
If so perhaps you could just use XLookup.

20230822 Lookup Indirect BT63.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3		Table Name -->		Table1	1	<-- Result
4		Lookup Value -->		Apple
5
6
7		Col1	Col2	Col3		Col1	Col2	Col3		Col1	Col2	Col3
8			Apple	1			Apple	4			Apple	7
9			Orange	2			Orange	5			Orange	8
10			Kiwi	3			Kiwi	6			Kiwi	9
11
Sheet1

Cell Formulas
Range	Formula
E3	E3	=XLOOKUP(D4,INDIRECT(D3&"[Col2]"),INDIRECT(D3&"[Col3]"), "")

 

[BT63]

Yes, they do. I will give this a try. Thank you very much.

 

[BT63]

Works perfect! I appreciate your help! I was just reading up on the XLOOKUP formula last night, so guess that was the answer. Thanks again and have a great day.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.