INDEX MATCH from two worksheets

[Ling Ling]

sheet5
https://i.stack.imgur.com/nbBHL.jpg
sheet6

sheet7

 

[Ling Ling]

sorry for the not complete message be shown

My complete question shown below :
I have a formula in `Sheet6` to look up a value from `Sheet5` and return it, but sometimes, if values are not in `Sheet5`, I want it to check in `Sheet7`.

`Sheet6` and `Sheet7` have same pattern in all column, only columns have different values.


How can I rewrite the formula in `Sheet 6` in order to check data in `sheet 5` first and if data were not found, then `Sheet7` will be automatically matched?


**Original formula in `Sheet 6`:**
=IF(ISNA(INDEX(Sheet5!$A$4:$AG$30,MATCH($C$6,Sheet5!C4:C30,0),2)),"",INDEX(Sheet5!$A$4:$AG$30,MATCH($C$6, Sheet5!C4:C30,0),2))


Screenshots below to clarify my question
[sheet5]: https://i.stack.imgur.com/nbBHL.jpg
[sheet6]: https://i.stack.imgur.com/YstX8.jpg
[sheet7]: https://i.stack.imgur.com/qf4UF.jpg

I‘ve tried, but seem not work
Hope anyone can try to provide help.
Any help appreciated

 

[NiMip]

What about:

=IFERROR(INDEX(Sheet5!$A$4:$AG$30,MATCH($C$6,Sheet5!C4:C30,0),2),INDEX(Sheet7!$A$4:$AG$30,MATCH($C$6, Sheet7!C4:C30,0),2))

The formula tries to calculate the first part of the IFERROR statement and if successful then you get your value from Sheet5. If it produces an error because it can't match the value in Sheet5, it calculates the second part instead and pulls the value from Sheet7.