Index match on multiple sheets

[CV12]

Hello,

I have got a formula where I search a value on one sheet. However, I would like to search on multiple sheets.

My formula is:

=INDEX(INDIRECT($H$8&"!D:D");MATCH(F38;(INDIRECT($H$8&"!G:G"));0))

In this formula, I am searching on the sheet name that is packed in the indirect

$H$8&"!

(2 times).

To $H$8&"!, I would like to add cell H9. How can I do this? I cannot find a solution by simply adding that cell to my formula. The columns D and G stay the same.

Any suggestions?

Thanks in advance

 

[Peter_SSs]

I presume that you mean if the lookup value is not found in the first sheet then you want to look in the second sheet?
If that is so, try this (after changing the formula separators from my commas to your semicolons )

=IFNA(INDEX(INDIRECT($H$8&"!D:D"),MATCH(F38,(INDIRECT($H$8&"!G:G")),0)),INDEX(INDIRECT($H$9&"!D:D"),MATCH(F38,(INDIRECT($H$9&"!G:G")),0)))