Jan 31 2020 03:07 AM
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
Jan 31 2020 03:30 AM - edited Jan 31 2020 03:31 AM
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
Jan 31 2020 03:42 AM
@Haytham Amairah Yes, the second one works! Thank You!
Any ideas on how to concatenate the names together?
Abi
Jan 31 2020 04:00 AM
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),"")))
Jan 31 2020 04:37 AM
Jan 31 2020 04:49 AM
Jan 31 2020 04:52 AM - edited Jan 31 2020 04:53 AM
See now that my formula only looks at JobNum. So it will go wrong if you have the same JobNum occurs with multiple Resources. Since this is the case, my solution is useless. But it was fun to create it.
Jan 31 2020 04:54 AM
Jan 31 2020 05:29 AM
You are welcome, thank you the confirmation.