Forum Discussion

Jared_Bold-Gooding's avatar
Jared_Bold-Gooding
Copper Contributor
May 07, 2025

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, and was hoping one of the gurus around here could point me in the correct direction.

I have ~20 sheets (multiple phases) tracking the status of work items, corresponding to particular users, and then marking off individual work items as they're completed for a given user within a phase. So far I've been doing this manually by logging into given systems, confirming a value is present, and then marking the corresponding column in the relevant sheet with a "Yes" for that user.

I would like to, instead, pull csv reports from the relevant systems, feed them into a "raw data" sheet at the front of the spreadsheet, and then have the formula for a given column lookup the target value for that user in the raw data, and return "Yes" if that value is there.

So at a high level:

=IF('RawData'!H2:H1500="*Windows Hello*",(INDEX('RawData'!A1:H1500,MATCH('UserReport'!A1:A100,'RawData'!A1:H1500,0),"Yes")))

ie;

  1. Look in the H column of sheet RawData for the value "Windows Hello"
  2. Index sheet RawData and compare value of user display name in column A with same column in UserReport sheet.
  3. Users' row in RawData sheet contains "Windows Hello", return value of "Yes" in the cell formula is applied to (The column in UserReport sheet which denotes if user has Windows Hello configured).

I understand that the "Yes" in the formula should actually be a column number where you want the value to return, but I want to print "Yes", not return a value from a column (Although I guess I could just make a dummy sheet with "Report values" and reference a cell with "Yes" in it?

Right now I'm just getting a SPILL error on the formula, so definitely something wrong in how I'm approaching this.

Please help!

excel
Formulas and Functions

2 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    May 08, 2025

    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.

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    May 08, 2025

    Difficult to visualize what you are doing, but the formula attempts to return a result in multiple cells at once. One or more cells in that so-called spill range aren't empty. That's causing the spill error. Just empty those cells and see if the formula returns the correct result. If not, come back and show a bit more with screenshots. Preferable, share a link that gives access to your file on OneDrive or similar.

Resources

OSZAR »