Which lookup do I need? And how do I use it?

[angeloudaki]

• I have a spreadsheet with 30+ tabs.
• All tabs have the same columnar setup
• I need to pull out all rows across all tabs which contain a single value found in a column cell (in this case; column C)
• Ideally, the report should include the tab name
• Some customer order numbers exist over different tabs (by date item shipped for example)
• In the example below I need a report of data in rows 1 and 2 for every tab in which that order number exists

e.g. ACTUAL IS MUCH MORE DETILED BUT EXCERPT EXAMPLE BELOW:

tab: 2023 06 08

	A	B	C	D	E
1​	order no	DTP PO	customer order no	date entered	product
2​	123456​	112​	ABC12345	02/05/2023​	M07
3​		112​	ABC12345	02/05/2023​	CARRIAGE
4​	789012​	113​	BDE67890	16/05/2023​	275V
5​	345678​	114​	CFG54321	22/05/2023​	4F10

 

[awoohaw]

@angeloudaki ,
Have you explored power query? If all the columns are identical you can combine all the worksheets into one big data table and do all the lookups you want.