Index/Match formula working in' Insert Function' Preview, but returns VALUE error on spreadsheet

[yateseyb]

Hello,

I've been working on an index/match formula on Excel to retrieve a value from a table based on two column criteria. When I do this via the Insert Function tool, it correctly previews the value it should lookup, but once I've clicked OK and returned to the spreadsheet, it returns a #VALUE! error.

All data in the spreadsheet is stored as text, and I'm nearly certain the formula is correct. The table it's looking up against is a big table (17 columns x 20,000 rows), yet it is still able to provide a result in the 'Insert Function' window?

=INDEX(I2:AB20610,MATCH(B3&$B$1,L2:L20610&O2:O20610,0),9)

Any help would be appreciated

 

[XOR LX]

Hi,

As it stands, that formula will need to be array-entered** in order to give you the desired result.

It appears to be a peculiarity of the Insert Function window that calculations are made on the array version of the formula, whether that formula has been committed as such in the actual worksheet or not.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).</SPAN></SPAN>

 

[yateseyb]

Well that was easy! Thank you, you've saved me a headache

 

[XOR LX]

You're welcome! Thanks for the feedback!