I have a formula from Excel that I'm trying to replicate in Numbers:
=IFERROR(LOOKUP(1000,SEARCH(References!$A$2:$A$99,$F15),References!$B$2:$B$99),"")
However, if I just try to import a worksheet with this formula, then Numbers tells me there's no corresponding formula and just imports the results instead. However, LOOKUP & SEARCH functions all seem to be available in Numbers. Any ideas? Thanks!
Hi mbp20111
Try the combination of INDEX and MATCH. That combination is more flexible and more powerful than the LOOKUP function.
The two tables can be on different Sheets (Tabs).
Formula in Table 1 Cell B2 =IFERROR(INDEX(References::B, MATCH(A2,References::A,0)),"Not found")
Fill down.
Please reply with questions.
Regards,
Ian.
Hi
This will work if you insert another column in Table 1 and type (or paste, as I did) the single search word Hippo.
Formula in Table 1 cell C2 =IFERROR(INDEX(References::B,MATCH(B2,References::A,0)),"Not found")
I am amazed that copying 非洲河马 from your reply actually works!
Regards,
Ian.
Edit, Sorry my reply had Rhino instead of Hippo.
This seems to be a sort of "off label" use of an Excel function that takes advantage of its ability to handle "array" type functions (where an array of values is held in memory). Short of a doing a short script, I can't think of a way to exactly replicate it in Numbers. However, you have a number of suggestions here to work with. Did you find any helpful?
SG
Thanks! But I’m running into trouble with that function for some non-alphabet languages.
For example, if I want to match “非洲河马” (African Hippo) to “河马” (Hippo). Only two of the characters are the same, and there are no spaces. Then this function doesn’t work. Is it possible to accommodate this case?
Hi mbp,
Sorry for the delay. I have been busy with my family over Christmas.
I tried several combinations of 非洲河马 in the References table to help the MATCH function.
I do not understand the language. Could you give some more examples to help?
Happy New Year!
Regards,
Ian.
Sorry, I've been busy with the holidays as well! Hope you had a good few days
I think it's just a matter of parsing characters vs parsing only whole words. For example, in English, it'd be the same to see if we can match "hippo" to "africanhippo" (one word).
For example, I've found that I can use IF(ISNUMBER(SEARCH to get Numbers to match "hippo" to "africanhippo". Rhe ISNUMBER command is very troublesome to use. I am hoping I can get the LOOKUP command to do something similar
I tried reformatting my original data to make it conform to the existing formulas, but unfortunately, that has proven to be even more troublesome. Is there any way to get this to recognize based on partial character match as opposed to word-match? e.g. match "hippo" to "africanhippo"
I've already managed to do this in Excel & Google Sheets (but not in Numbers unfortunately) with the following command: =IFERROR(LOOKUP(1000,SEARCH(References!$A$2:$A$99,$F15),References!$B$2:$B$99),"")
I can't figure out what you are doing with those Excel formulas. But I thought I'd point out the existence of the * wildcard.
So, for example, you can do this:
=MATCH("*"&A2&"*",References::A,0)
So both 非洲河马 and 河马 find a match with the 非洲河马 in the References table.
I don't think it is easy to do it the other way around, though, i.e., have 非洲河马 in the first table and find a match with just 河马 in the second, unless you know you always will have just the last two characters to match, in which case you could do something like this:
=MATCH("*"&RIGHT(A2,2)&"*",References::A,0)
There are also various approaches using REGEX.
SG
Here's what I did in Excel. The first screenshot is the reference table. And the 2nd image is the table I want matched. Note that I can put any number of random characters before "河马" or Hippo and still have it match correctly
Here is the Excel file if you want to take a look: https://1drv.ms/x/s!AoyxytEAaZ1JiB0waveUyiAXET-z?e=84ib0W
Note it also translated to Google Sheets: https://drive.google.com/file/d/16Oy13cxVu2id2vpOnv8lLBDOGCULtDUj/view?usp=sharing
Unfortunately, this function doesn't automatically translate to iWork Numbers.
There are also various approaches using REGEX.
Sorry, I'm not really familiar with REGEX. Can you show me an example of how to accomplish this with a REGEX function?
Sorry Ian, I dont mean to rush you. I have been experimenting with some functions in Numbers myself, but alas none of them have worked. I'm not too familiar with the program, I'm afraid. Do you have any further thoughts?
Hi SG,
I was really hoping to get it working in Numbers, as there were other Numbers functions that I am not able to replicate in Excel/Google Sheets...
In Numbers, I was able to get it to match by character as opposed to whole words by using: IF(ISNUMBER(SEARCH
However, this is rather tedious as I have to write the search terms into the function. Not very flexible. Is there any way to get this result using a list I can just refer to?
Unfortunately, my data is structured like "非洲河马" where there's often only a partial keyword match. Is it possible to match it in those cases?
Replicating LOOKUP & SEARCH functions in Excel
