#INVALID VALUE or empty cell when using INDEX MATCH formula
As you see in the screenshot when using the formula below, it worked perfectly in the first column (Interim Handover Letter), however, it started to give me either (blank/empty cells) and (#INVALID VALUE) when applying it to the rest of the columns.
I'm trying to return a value from (column number 12) a reference sheet by finding 2 matched values.
=INDEX({FLOC Docs Submission Info-ETS_Aug11 Range 3}, MATCH(Site@row, {FLOC Docs Submission Info-ETS_Aug11 Range 4}, 0) * MATCH("Interim Handover Letter", {FLOC Docs Submission Info-ETS_Aug11 Range 2}, 0), 12)
I saw a suggestion to use "@cell = 0" but it didn't work!
Here is a screenshot of the reference sheet:
Please help π
Best Answers
-
The INDEX/COLLECT should be working. I have used it quite a bit in the past.
=INDEX(COLLECT({Source Sheet Column To Pull From}, {FLOC Docs Submission Info-ETS_Aug11 Range 4}, @cell =Β Site@row,Β {FLOC Docs Submission Info-ETS_Aug11 Range 2},Β @cell = "Interim Handover Letter"), 1)
Your first range should be created by clicking on the Status column header of the source sheet.
The second range would be created by clicking on the Site column header of the source sheet.
And the third range should be created by clicking on the Systems/Geography column header fo the source sheet.
A few things I have seen in the past...
Make sure you are including that last argument. Your formula should end with
), 1)
When you create your cross sheet reference, give the sheet a few seconds to load before clicking on the column header. If one or both sheets are really busy or if your internet is running a little slow, you can actually click on the column header before the source sheet has fully loaded. When the source sheet finally does fully load, the selection will revert back to the default of the "home cell" which is the cell in the top left corner of the sheet. It won't warn you or anything. This one gets me quite a bit.
-
Happy to help. ποΈ
Which was it? The last argument missing or clicking too fast when creating cross sheet references? I do both more often than I care to admit. Hahaha
Answers
-
Why exactly are you matching on two values? I assume you need to pull from column 12 based on both of those values being present in the same row?
If that is the case, try this instead...
=INDEX(COLLECT({Source Sheet Column To Pull From}, {FLOC Docs Submission Info-ETS_Aug11 Range 4}, @cell = Site@row,Β {FLOC Docs Submission Info-ETS_Aug11 Range 2},Β @cell = "Interim Handover Letter"), 1)
-
Hi Paul,
it gave me "#INCORRECT ARGUMENT SET"
I'm trying to return the value from column "FLOCT Docs status" by finding the site name in column "Site" and the category of delivery from site "Systems/Geography"
Like what is the status of the task "Interim Handover Letter" of the site "1000 EMSF"
-
=INDEX(collect({FLOC Docs Submission Info-ETS_Aug11 Range 3}, {FLOC Docs Submission Info-ETS_Aug11 Range 4},Site@row, {FLOC Docs Submission Info-ETS_Aug11 Range 2},"Interim Handover Letter"),1)
Might work for you if i'm understanding what you are trying to do.
-
Unfortunately Luke it gave me the same results "#INCORRECT ARGUMENT SET"
-
Did you double check your ranges and make sure they are all the same size?
-
@Luke Warner I just did, there are changes as you see, but most of the cells have errors, this is my original formula Index Match, the Index Collect never worked
-
I'm basically traying to return a value from multiple criteria
-
The INDEX/COLLECT should be working. I have used it quite a bit in the past.
=INDEX(COLLECT({Source Sheet Column To Pull From}, {FLOC Docs Submission Info-ETS_Aug11 Range 4}, @cell =Β Site@row,Β {FLOC Docs Submission Info-ETS_Aug11 Range 2},Β @cell = "Interim Handover Letter"), 1)
Your first range should be created by clicking on the Status column header of the source sheet.
The second range would be created by clicking on the Site column header of the source sheet.
And the third range should be created by clicking on the Systems/Geography column header fo the source sheet.
A few things I have seen in the past...
Make sure you are including that last argument. Your formula should end with
), 1)
When you create your cross sheet reference, give the sheet a few seconds to load before clicking on the column header. If one or both sheets are really busy or if your internet is running a little slow, you can actually click on the column header before the source sheet has fully loaded. When the source sheet finally does fully load, the selection will revert back to the default of the "home cell" which is the cell in the top left corner of the sheet. It won't warn you or anything. This one gets me quite a bit.
-
@Paul Newcome It worked! Thank you very much π·ββοΈ
-
Happy to help. ποΈ
Which was it? The last argument missing or clicking too fast when creating cross sheet references? I do both more often than I care to admit. Hahaha
Help Article Resources
Categories
Check out the Formula Handbook template!