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
1	Crit1	Crit2	Value
2	A	X	1
3	A	Z	2
4	A	Y	3
5	B	Y	4
6	B	Z	5
7
8	Crit1	Crit2	Return
9	B	Z	5

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula C9 {=INDEX($C$2:$C$6, MATCH(A9&B9, $A$2:$A$6&$B$2:$B$6, 0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

/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!