Forum Discussion

A819A1L's avatar
A819A1L
Brass Contributor
Jan 31, 2020

VLOOKUP with multiple criteria (trying to work around SPILL error)

Hi. I really need help with the SPILL error. I know it's been an issue since the update last year and the solution is to select a specified range rather than an entire column, but the result only partially works. See spreadsheet attached.

 

I'm trying to lookup a text value based on two criteria in two different columns. eg:

=IF(G6=A6,VLOOKUP(B6,H:J,3,0),0)

 

This works but only if the matching values are on the same line, which is rarely the case.

 

Before the update I would have used this which now returns the SPILL error:

=IF(G:G=A2,VLOOKUP(B2,H:J,3,0),0)

 

I'm also trying to concatenate the names together but can't find a formula that will work with a VLOOKUP.

 

Thanks in advance for any help!

 

Abi

 

excel

8 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jan 31, 2020

    A819A1L 

    If your Excel version allows it, this one will work:

    =TEXTJOIN(", ",,UNIQUE(IFERROR(INDEX(J:J,IFERROR(1/(B55=$H$2:$H$103)*ROW($H$2:$H$103),""),1),"")))

     

    • A819A1L's avatar
      A819A1L
      Brass Contributor
      Jan 31, 2020
      Spoiler
      It works on bringing the text together however, the results are incorrect as I'm trying to separate the resource criteria i.e. return the operator to the associated machine.
  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Jan 31, 2020

    A819A1L

     

    Hi,

     

    Try this formula instead:

    =IF(OR(A2=G:G),VLOOKUP(B2,H:J,3,0),0)

     

    Or this one below which is faster:

    =IF(MATCH(A2,G:G,0),VLOOKUP(B2,H:J,3,0))

     

    Hope that helps

Resources