INDEX / MATCH without values

[thorpyuk]

Hi,

I want to return a string on an index/match, but can't quite figure out exactly what the formula should be.


=INDEX(Input!$G$2:$G$1461,MATCH(C$5,IF(Input!$A$2:$A$1461=$A9,Input!$J$2:$J$1461),0))

Something like that, where column J is a date, and column A is matching to the string in cell C5, and column G is what i want to return - in this instance it should return 'Investment'

For some reason it's giving me an error

Thanks guys :/

 

[adam087]

Does this formula do what you want, adapt as per your own references...

Excel 2010

	A	B	C
Crit1	Crit2	Value
A	X
A	Z
A	Y
B	Y
B	Z
Crit1	Crit2	Return
B	Z

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]5[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C9[/TH]
[TD="align: left"]{=INDEX($C$2:$C$6, MATCH(A9&B9, $A$2:$A$6&$B$2:$B$6, 0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



/AJ

 

[Andrew Poulsom]

Try:

=INDEX(Input!$G$2:$G$1461,MATCH(1,INDEX((Input!$A$2:$A$1461=$A9)*(Input!$J$2:$J$1461=C$5),),0))

 

[thorpyuk]

Thanks both!
Andrew - what was i doing wrong?

 

[Andrew Poulsom]

Your formula works for me if confirmed with Ctrl+Shift+Enter.

 

[Aladin Akyurek]

Try:

=INDEX(Input!$G$2:$G$1461,MATCH(1,INDEX((Input!$A$2:$A$1461=$A9)*(Input!$J$2:$J$1461=C$5),),0))

Thanks both!
Andrew - what was i doing wrong?

You probably forgat to confirm the formula with control+shift+enter, not just enter, for

=INDEX(Input!$G$2:$G$1461,MATCH(C$5,IF(Input!$A$2:$A$1461=$A9,Input!$J$2:$J$1461),0))

is equivalent to Andrew's suggestion.

 

[thorpyuk]

Thanks for your help everyone!