index match with multiple criteria

[valmir]

Dear Sirs
I don't know if what I am looking for has a specific name but googling my title, I had dozens of examples of formulas but none of them is even close to what I'm looking for. So, when that happens, I wonder if it is even possible.
This formula is working great!
=INDEX(INDIRECT($A30&"!$R$2:$FK$51");50;MATCH("S1";INDIRECT($A30&"!$R$2:$FK$2");0))
Problem is, I can have as lookup value, either S1 or S2, meaning when the value is S1, of course the formula works and when it is S2, obviously it doesn't. My question is:
Can I have the formula to look for both S1 and S2, instead of just for S1?
That's the first request.
Can it also be modified for 4 values? For example Q1, Q2, Q3 and Q4?
Thanks!

 

[Glenn Kennedy]

Not sure what you mean by "both".. Both at the same time????

=INDEX(INDIRECT($A30&"!$R$2:$FK$51");50;MATCH(A1;INDIRECT($A30&"!$R$2:$FK$2");0))

where A1 contains S1 or S2, or Q1 or.....

 

[valmir]

Not sure what you mean by "both".. Both at the same time????

=INDEX(INDIRECT($A30&"!$R$2:$FK$51");50;MATCH(A1;INDIRECT($A30&"!$R$2:$FK$2");0))

where A1 contains S1 or S2, or Q1 or.....

No! I can have only one of the two. It will be either S1 or S2. In the other case, I may have only one of Q1 to Q4, i.e. (Q1, Q2, Q3, Q4)

 

[Glenn Kennedy]

So, try the formula suggested.

 

[valmir]

So, try the formula suggested.

Sorry, this formula doesn't work because I have to include the lookup values in the formula, not a reference cell, because I can't have for example S1 and S2 in a single cell. I hope my explanation makes sense...

 

[valmir]

Can you please help me with another formula, instead?
The lookup value is in cell H8 (exact match)
The formula needs to lookup for this value in the range R2:FK2
Based on the match found in R2:FK2, formula needs to return the corresponding column value in the range R51:FK51
Thanks!

 

[valmir]

Can you please help me with another formula, instead?
The lookup value is in cell H8 (exact match)
The formula needs to lookup for this value in the range R2:FK2
Based on the match found in R2:FK2, formula needs to return the corresponding column value in the range R51:FK51
Thanks!

I found a solution!