Index - match - indirect

**JAYZE** · 03-30-2016, 11:36 AM

This INDEX and MATCH formula work fine
=INDEX('FLEX SMOOTH'!$B$3:$B$27,MATCH(Sheet1!$C$9,'FLEX SMOOTH'!$A$3:$A$27,0))

It is looking for numeric value input at Sheet1 C9 in the sheet FLEX SMOOTH A2:A27. It then returns a corresponding text string from FLEX SMOOTH B3:B27

There are now 4 other sheets with similar information in the same ranges. I have used a drop down at Sheet1 C6 to filter the sheets then want to use INDIRECT BUT this sysntax just gives me a #REF!

=INDEX(INDIRECT("'"&C6&"'!$B$3:$B$27"),MATCH(Sheet1!$C$9,INDIRECT("'"&C6&"'!$A$3:$A$27"),0))

Very grateful for any assistance. Thanks in advance.

Jayze

**Tony Valko** · 03-30-2016, 11:43 AM

What sheet is the formula entered on?

Maybe you need the sheet name with C6?

Sheet1!C6

**JAYZE** · 03-30-2016, 11:50 AM

The formula is on Sheet1 ....with the drop down

Tried your suggestion, still no go. Thanks.

=INDEX(INDIRECT("'"&Sheet1!$C$6&"'!$B$3:$B$27"),MATCH(Sheet1!$C$9,INDIRECT("'"&Sheet1!$C$6&"'!$A$3:$A$27"),0))

**Jonmo1** · 03-30-2016, 11:55 AM

If that's returning #REF! error, that pretty much means the sheet named in C6 doesn't actually exist.
Check for exact spelling and extra spaces in BOTH the cell C6, and the actual tab names.

**Tony Valko** · 03-30-2016, 11:57 AM

The formula is syntactically correct.

I guess we'll need to see the file to find the problem.

**Ochimus** · 03-30-2016, 11:58 AM

JAYZE

Attached does what I understand you want?

In Sheet1, you specify the sheet you want to use in C6, and what you want to match in C9, and the formula pulls up the correct number in E6 (should have highlighted that, sorry).

Ochimus