Index Match Two Ranges

[BWMagee]

Hi, I am trying to match a value that might appear in one of two columns.

My working in english is this:
IF (Sheet 2 Column D = Sheet1 A2) AND (Sheet2 Column A = Sheet1 H1)
INDEX (Return value from Sheet 2 Column C)
OR
IF (Sheet 2 Column C = Sheet1 A2) AND (Sheet2 Column A = Sheet1 H1)
INDEX (Return value from Sheet 2 Column D)

Where:

Sheet1
H1 (contains numerical value "1")
A2 (contains text "Home")

Sheet2
Column A (contains numerical values, "1,2,3" etc... equivalent to H1 in Sheet1)
Column C & D (contains text "home" and "away" ... equivalent to A2 in Sheet2)

If I just searched one range, this formula works:
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Calibri; color: #000000}</style>
=INDEX(Sheet2!$C$1:$C$280,MATCH(1,(Sheet1!$H$1=Sheet2!$A$1:$A$280)*(Sheet1!$A2=Sheet2!$D$1:$D$280),0))

That returns the right value from column C, when we search D for A2. But how do I also return the right value from Column D, when we search C for A2? It would be great if after the formula above I could just write OR(INDEX(.... and just swap the "D" for "C" and vice versa. But I tried that and it didn't work.

Any help much appreciated!

 

[inactiveuserps07]

@BWMagee, I know that what you explained feels clear to you, because you have your sheet in front of you. But for the rest of us, it's not quite as easy to get a grasp of what you're trying to do, where the formulas/results should be going, etc. Can you post some actual data from each sheet, including Column headers and Row numbers, as well as a representation of the desired results placed where you'd like them relative to the other data?

 

[BWMagee]

@BWMagee, I know that what you explained feels clear to you, because you have your sheet in front of you. But for the rest of us, it's not quite as easy to get a grasp of what you're trying to do, where the formulas/results should be going, etc. Can you post some actual data from each sheet, including Column headers and Row numbers, as well as a representation of the desired results placed where you'd like them relative to the other data?

Sure thing, appreciate the response!

Sheet1

	A	H	I
2	Home	1	=Formula

<tbody>
</tbody>

Sheet2

	A	C	D
1	1	Home	Neutral
2	1	Away	Home
3	1	Neutral	Away
4	2	Away	Neutral
5	2	Home	Away
6	2	Neutral	Home
7	3	Home	Away

<tbody>
</tbody>

So for my formula:
1) Text in Sheet1 H2 ("1") must be found in Sheet2 ColumnA (in this case, A1, A2, A3).
2) When text in Sheet1 A2 ("Home") is found in Column C, I want to return the value in Column D. (in this case row1 = "Neutral")
3) When text in Sheet1 A2 ("Home") is found in Column D, I want to return the value from Column C (in this case row2 = "Away").

In each case there will never be a duplicate of "Home". (Eg. When Column A has text "1", then text "Home" will never be found in both C and D. It will always be one or the other.)

Hope that helps make things clearer!

 

[Aladin Akyurek]

What is the expected result here: Neutral or Away or both Neutral and Away?

 

[BWMagee]

What is the expected result here: Neutral or Away or both Neutral and Away?

Sorry, I can see I've made an error in my explanation, with "Home" appearing twice in A1. There won't be duplicates so that shouldn't happen. I'll simplify the table:

	A	C	D
1	1	Home	Away
2	1	Hello	Goodbye
3	1	Please	Anger
4	2	Away	Home
5	2	Anger	Please
6	2	Goodbye	Hello
7	3	Anger	Please

<tbody>
</tbody>

In this new example, with "Home" only appearing once when A=1, the expected value would be "Away".

 

[inactiveuserps07]

@BWMagee, there are many ways to do this. The following array formula is one (be sure to use Ctrl+Shift+Enter):

=IFERROR(SUBSTITUTE(INDEX(Sheet1!$C$1:$C$300&Sheet1!$D$1:$D$300,MATCH("*"&$H$2&"*"&$A$2&"*",Sheet1!$A$1:$A$300&Sheet1!$C$1:$C$300&Sheet1!$D$1:$D$300,0)),$A$2,""),"*no match*")

 

[Aladin Akyurek]

Sorry, I can see I've made an error in my explanation, with "Home" appearing twice in A1. There won't be duplicates so that shouldn't happen. I'll simplify the table:

	A	C	D
1	1	Home	Away
2	1	Hello	Goodbye
3	1	Please	Anger
4	2	Away	Home
5	2	Anger	Please
6	2	Goodbye	Hello
7	3	Anger	Please

<tbody>
</tbody>

In this new example, with "Home" only appearing once when A=1, the expected value would be "Away".

Just enter:

=SUBSTITUTE(LOWER(LOOKUP(9.99999999999999E+307,1/((Sheet2!$A$1:$A$7=$H2)*((Sheet2!$C$1:$C$7=$A2)+(Sheet2!$D$1:$D$7=$A2))),Sheet2!$C$1:$C$7&Sheet2!$D$1:$D$7)),LOWER($A2),"")

 

[BWMagee]

Thanks both for your replies, greatly appreciated!

@BWMagee, there are many ways to do this. The following array formula is one (be sure to use Ctrl+Shift+Enter):

=IFERROR(SUBSTITUTE(INDEX(Sheet1!$C$1:$C$300&Sheet1!$D$1:$D$300,MATCH("*"&$H$2&"*"&$A$2&"*",Sheet1!$A$1:$A$300&Sheet1!$C$1:$C$300&Sheet1!$D$1:$D$300,0)),$A$2,""),"*no match*")

Returns *no match* I'm afraid.

Just enter:

=SUBSTITUTE(LOWER(LOOKUP(9.99999999999999E+307,1/((Sheet2!$A$1:$A$7=$H2)*((Sheet2!$C$1:$C$7=$A2)+(Sheet2!$D$1:$D$7=$A2))),Sheet2!$C$1:$C$7&Sheet2!$D$1:$D$7)),LOWER($A2),"")

Brilliant, works perfectly!

 

[inactiveuserps07]

@BWMagee, yup, I only just noticed that your Sheet1 has the H value, not Sheet2; so my formula should have had Sheet2 in all Sheet1 places:

[COLOR=#333333]=IFERROR(SUBSTITUTE(INDEX(Sheet2!$C$1:$C$300&Sheet2!$D$1:$D$300,MATCH("*"&$H$2&"*"&$A$2&"*",Sheet2!$A$1:$A$300&Sheet2!$C$1:$C$300&Sheet2!$D$1:$D$300,0)),$A$2,""),"*no match*")[/COLOR]

Moot point, however, since you've got Aladin's working.

 

[Aladin Akyurek]

Glad to help.