Forum Discussion
Using IF, INDEX, & MATCH for complex lookup
Hi folks, I've been trying to figure out a bit of a (seemingly) complex IF, INDEX, & MATCH statement to achieve some reporting on raw data that I'm gathering, but can't quite seem to figure it out, ...
Few more comments
'RawData'!H2:H1500="*Windows Hello*"most probably return 1499 FALSE values. "*" doesn't work as wilcard here, it's taken as text literally. If in column H we try to find exact value Windows Hello, when remove "*" . If it is part of the text in the cell, when
ISNUMBER( SEARCH( "Windows Hello", RawData!H2:H1500 ) )Next, MATCH doesn't work on 2D array. Thus
MATCH('UserReport'!A1:A100,'RawData'!A1:H1500,0)returns 1D array with 99 #N/A errors. If you lookup in column A only, when
MATCH('UserReport'!A1:A100,'RawData'!A1:A1500,0)again returns 99-size array, but now one or few values in it will be position(s) of 'UserReport'!A1:A100 within 'RawData'!A1:H1500, the rest (for not matched values) are !N/A
In any case your formula returns array (most probably all with #N/A), most probably you have no room in the grid to place these 1500 values, thus #SPILL! error.