Combining MATCH and INDIRECT functions with dynamic cell references in spreadsheets

Other Questions Google Sheets
1

I’m scratching my head over a spreadsheet puzzle. I’m trying to use MATCH and INDIRECT together with dynamic cell references. Here’s what I’ve done so far:

  1. Made a sheet called ‘Project Budget’
  2. Put ‘Monthly Expenses’ in cell E20 of that sheet
  3. Tested INDIRECT: =INDIRECT("Project Budget!E20") - works fine, shows ‘Monthly Expenses’
  4. Tested MATCH: =MATCH("Monthly Expenses","Project Budget!E20") - also good, returns 1

But when I try to combine them:

=MATCH("Monthly Expenses",INDIRECT("Project Budget!E20"))

I get an error: #NA - Can’t find ‘Monthly Expenses’ in MATCH evaluation.

What I really want to do is something like:

=MATCH("Monthly Expenses",INDIRECT("" & $B8 & "!E:E"))

Where B8 has the sheet name, and it searches the whole E column for ‘Monthly Expenses’.

Any ideas on what I’m doing wrong? Thanks for any help!

2

I’ve run into similar issues before, and I think I see where the problem is. When you’re using INDIRECT with MATCH, you need to make sure you’re giving MATCH a range to search in, not just a single cell.

Try changing your formula to this:

=MATCH(“Monthly Expenses”,INDIRECT(“Project Budget!E:E”))

This should work because now you’re searching the entire column E for “Monthly Expenses”.

For your more complex formula with the dynamic sheet name, you’d want:

=MATCH(“Monthly Expenses”,INDIRECT($B8 & “!E:E”))

Note that you don’t need the extra quotes around the sheet name reference.

One thing to watch out for: make sure your sheet names don’t have spaces. If they do, you’ll need to add single quotes around the sheet name in the INDIRECT function.

Hope this helps! Let me know if you run into any other snags.

3

I’ve encountered this issue before. The problem lies in how MATCH and INDIRECT interact. MATCH requires a range to search through, not a single cell value.

To fix this, modify your formula to:

=MATCH(“Monthly Expenses”,INDIRECT(“Project Budget!E:E”))

This searches the entire column E for “Monthly Expenses”.

For your dynamic sheet reference, use:

=MATCH(“Monthly Expenses”,INDIRECT($B8 & “!E:E”))

Remember, if your sheet names contain spaces, enclose them in single quotes within the INDIRECT function.

Also, ensure the cell containing “Monthly Expenses” is exactly as typed in your MATCH function, including capitalization and spaces. Any discrepancy will result in a #N/A error.

4

hey mate, i think i get what ur tryin to do. the thing is, INDIRECT needs a range, not just one cell. try this:

=MATCH(“Monthly Expenses”,INDIRECT($B8 & “!E:E”))

that should work for ya. good luck!