You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
Sorry to ask you all, as I'm sure this is so simple it's embarrasing to ask.
I have a column of numbers, not in any sequencial order, and with some gaps.
I'm using INDEX and MATCH to look for a number in the col and return a value from a different col, which is fine if the number is in the list, but gives an error if it isn't. What should I do so that I can return a 'Not Found' if the number isn't on the list?
The answer is the same as all the myriad other bloody Excel Help threads. VLOOKUP.
Its not a vlookup answer!
Look at the help for ISERROR that should help. (and IFERROR might be possible too - apparently IFERROR returns your error message OR the content of the input if it is not an error).
VLOOKUP is rubbish.
If the lookup data is in colA and the extracted data is in colB, use
=IF(ISERROR(MATCH(target_number,A1:A100,0)),"number ain't there",INDEX(B1:B100,(MATCH("target number",A1:A100,0)))
there's also a version which use IFERROR, which is maybe simpler but slightly less flexible.
=IFERROR(INDEX(B1:B100,MATCH(target_number,A1:A100,0)),"ain't there")
VLOOKUP is rubbish.
Worng
=IF(ISERROR(MATCH(target_number,A1:A100,0)),”number ain’t there”,INDEX(B1:B100,(MATCH(“target number”,A1:A100,0)))
Also worng - you're missing a parentheses :p
OP: nest your Index(Match()) inside IFNA()
You spelt wrong wrong.
=IF(ISERROR(MATCH(target_number,A1:A100,0)),”number ain’t there”,INDEX(B1:B100,MATCH(“target number”,A1:A100,0)))
Okey Doke.
Yes LOOKUP is duff as the numbers do not run sequentially, it's actually a set of serial number references.
I'll see what I can do with the ERROR functions.
Doom!
Done.
=IFERROR(CONCATENATE((INDEX(batt_sn,MATCH(A5,installed_in,0)))," - ",(INDEX(percent_used,MATCH(A5,installed_in,0))),"%"),"Not Known")
Worked like a charm.
Now someone is going to tell me that what I done is pure muppetry and then tell me what I should do.
You spelt wrong wrong.
That's the joke
LOOKUP is duff as the numbers do not run sequentially
Maybe so, but vlookup() need not be sequential unless you're using it to do a binary search.
Now someone is going to tell me that what I done is pure muppetry and then tell me what I should do.
If it works, it works. I'd use ifna() as it's specifically for missing values instead of all errors.
VLOOKUP has its limitations but :
=IF(ISNA(vlookup(I32,G33:H48,2,false)),"Not Found",VLOOKUP(I32,G33:H48,2,FALSE))
where I32 is the value you want to find and G33:H48 is the range to search in.
No need to do the VLOOKUP twice, IFERROR(VLOOKUP(I32,G33:H48,2,FALSE),”Not Found”)
(Or IFNA(...) if you prefer).
If you’ve got a lot of data, it may be faster. Also, consider index/match as it’s a lot easier to maintain as you can move columns about. Also let’s you look for a value in a column to the right of the one you want. Just remember to set match type 0 or you may get unexpected results!