I am trying to create a search box in a google sheet that finds the value of a cell from the time row and the date column in a specific tab
I have found the formula to use when I can enter the specific tab name manually:
=INDEX('First Tab'!B2:D5, MATCH(B2,'First Tab'!A2:A5,0), MATCH(B1,'First Tab'!B1:D1,0))
But I am unable to figure out how to incorporate the indirect function so that I can use a cell that shows the tab name, rather than the tab name itself.
I have tried the following and just get a formula parse error
=INDEX(INDIRECT('"&B3&"'!B2:D5, MATCH(B2,(INDIRECT('"&B3&"'!A2:A5,0), MATCH(B1,(INDIRECT('"&B3&"'!B1:D1,0))))))
Cell B3 houses the name of the tab that I want to look at ('First Tab') Cell B2 house the name of the time I'm looking for Cell B1 houses the date I'm looking for
Here is the link to the google sheet
https://docs.google.com/spreadsheets/d/1TpzvfpNBTFksuFTJ1_hp51LhpPzcE8vKMFOvi1R-8D4/edit?usp=sharing
Any help would be appreciated
Thanks!
The error you are getting is due to these reasons:
You are missing double qoutes " for string concatenation in INDIRECT which prevents proper concatenation of the sheet name from B3
Incorrect Placement of MATCH's search_type: Placing the 0 (for exact match) inside the INDIRECT function's syntax is syntactically incorrect for the MATCH function. The search_type needs to be a separate, third syntax of MATCH.
=INDEX(INDIRECT("'"&B3&"'!B2:D5"), MATCH(B2,INDIRECT("'"&B3&"'!A2:A5"),0), MATCH(B1,INDIRECT("'"&B3&"'!B1:D1"),0))