You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
I'm normally OK with the help of Google but this has me stumped.
Effecitively I've got a sheet that prints a list of sample details based on manually-entered sample IDs.
There are a few hundred samples one the sheet, numbered e.g. LEV_20220114_001, LEV_20220114_002, etc. based on the date they were collected, with columns for each sample that give various details about the sample. I'm pulling a couple of details onto a separate sheet so we can print labels, using the =INDEX() function and the sample ID and everything works perfectly...
...until today.
Now it doesn't seem to recognise there is any data entered past a certain sample ID and just pulls across info from the last recognised sample details. Say I've got sample details from 20220201_001 through _055, it'll only recognise up to _016 and any number past _016 I use in the =INDEX() will only pull across the data from _016.
Any ideas why this might happen and what I can do to solve it?
There are no changes in how the sample IDs are stored or formatted, there are no changes in the formulas, the label sheet will bring across all the correct info for all the samples ever entered up to 20220201_016, just nothing after.
How many entries are you handling? What format is the sheet (CSV, xls, xlsx ods and so on)
It's the data range you're indexing large enough (eg a1:a017 not a1:a016)?
There's only a couple of hundred samples so far, not even close to where I'd think Excel was struggling. Data range is just A:A, A:L, etc. and it's a .xlsx file.
Are you using the Match() function to get the record you want? Remember to put 0 at the end in the optional argument to get the behaviour you probably expect.
(Otherwise, if the data isn't in alphabetical order, you can get odd results - it could be the name for _17 isn't in order or something.)
Edit - as for number of rows.. you're looking at many thousands before it starts to slow down and I think modern versions should get the right answers up to a million rows.
Hmmm. Is it doing *anything*? Returning n/a etc or just merrily ignoring you (I'm inclined to think the former given you mentioned it pulls other data but that could be historical depending on how your sheet is set up.)
No errant blanks or hidden rows?
RM.
What does the formula look like?
E.g. =index(a:c,match([sample ID],A:A),3)
No blank rows, no syntax errors - I've copied sample details that do work into the sample IDs that don't work and it just ignores them. Yes, it's nested MATCH() inside the INDEX()
If I only have data down to _016 and blank rows below that and I try to index data from _024, it'll just bring the last data it can find - that being from _016. That behaviour I can understand. But it's doing that even though there is data below row _016. It's as though there's an Excel brain-fart stopping it seeing any data below a certain row.
What does the formula look like?
=INDEX('Sample Data'!$A:$L,MATCH(J3,'Sample Data'!$A:$A),4)
Where J3 = the cell containing the sample ID reference
As euain suggests try
=INDEX(‘Sample Data’!$A:$L,MATCH(J3,’Sample Data’!$A:$A ,0),4)
First guess is it's picking up a "close enough" match
yep. MATCH type 0 omission error.
I think it was a corrupted network file sync. I've just copied each sheet cell-for-cell to a new file and it all works fine now.
I'll added the ,0 to my MATCH just in case.
Cheers.