I’m not a power Excel user, but as we handle a lot of data, I had to use it earlier today to do some in_array type searching. Basically, I had a CSV with a load of IDs, but we’d already processed some of the IDs. I wanted to remove the IDs from the CSV that we’d already processed. So I have this CSV of IDs, and a list of IDs we’d already processed. Great! In PHP, I could just use in_array on each of them. But I didn’t have time to do a PHP script, so this is how to do a similar thing in Excel – a Vertical Lookup.
Open your CSV in Excel, and add two new columns “vlookup” and “is_processed”. The formula to apply should be something like:
=VLOOKUP(C2,'already processed'!A:A,1,TRUE)
C2 is the ID you want to check if is in the list of already processed IDs (the “needle”). If you want the matching to be non-strict and have the lookup table sorted in whatever order (probably not), set the last parameter to TRUE. Normally you want FALSE to check anywhere, and make sure you sort the lookup table in ascending order.
Add a new sheet, and call it “already processed”, and add your list of IDs in the first column. This is your “haystack”.
If you return to your original sheet, you’ll see the “vlookup” column has either IDs (if the ID is found in your list) or #N/A if the value is not found. To make this neat, add this formula to your is_processed column:
=IF(ISNA(F2),"no","yes")
Where F2 is the vlookup column. Then for the IDs (“needles”) that are found in the search list (“haystack”) the value of is_processed will be “yes”, and all that aren’t found it’ll be “no”. You can then filter on this or whatever you like!
