Google Sheets - Index/Match Two Values

[chrono2483]

Hello,

I am hoping I could get some assistance with a formula in google sheets. I want to use an index/match formula, to return a value should it match two values in my current sheet. I currently am using this formula:

=iferror(index(Sheet1!$F$1:$F$500,match(D246,arrayformula(Sheet1!$A$1:$A$500),0)),"")

This does return the value I want. However, now i want to match two values: D246 and F246. Is there a way to incorporate this in the existing formula? Both will be found in the same range: Sheet1!$A$1:$A$500

Thank you.

 

[FDibbins]

So will each value be in it's own cell, or both in the same cell?

 

[chrono2483]

So will each value be in it's own cell, or both in the same cell?

The value returned would be a single value, in a single cell (H246, as example). I would like the formula to match two different values, and return the corresponding answer. If D246 = "Bruce" and F246 = "Sheet1", the formula will know to search in sheet1.

 

[FDibbins]

Don't use google sheets, but 1 way to do this would be to create a helper column to combine the 2 entries, then base the MATCH on that

 

[Aladin Akyurek]

[...]

=iferror(index(Sheet1!$F$1:$F$500,match(D246,arrayformula(Sheet1!$A$1:$A$500),0)),"")

ARRAYFORMULA(Sheet1!$A$1:$A$500) is exactly equivalent to Sheet1!$A$1:$A$500, so we don't need the ARRAYFORMULA call.

What does matching D246 and F246 against Sheet1!$A$1:$A$500 mean? (a) Both must match such that a single (the same) value from Sheet1!$F$1:$F$500 or (b) either D246 or F246 must match?

 

[chrono2483]

ARRAYFORMULA(Sheet1!$A$1:$A$500) is exactly equivalent to Sheet1!$A$1:$A$500, so we don't need the ARRAYFORMULA call.

What does matching D246 and F246 against Sheet1!$A$1:$A$500 mean? (a) Both must match such that a single (the same) value from Sheet1!$F$1:$F$500 or (b) either D246 or F246 must match?

Thanks Aladin - you are right! I corrected/removed the ARRAYFORMULA call. To your question, both must match, so that it returns the value from Sheet1. The exact same method as the original example with D246, but instead of 1 match, I want 2 matches (if that makes sense).

 

[Aladin Akyurek]

Thanks Aladin - you are right! I corrected/removed the ARRAYFORMULA call. To your question, both must match, so that it returns the value from Sheet1. The exact same method as the original example with D246, but instead of 1 match, I want 2 matches (if that makes sense).

Let's say that D246 matches A10 and F246 A12 in column A (yielding 10 and 12). What must be the output from column F when we have these two matches?