Hi,
I have a small animal database which has the animal name and the number of legs it has.
I want to create a searchable database, whereby I type the animal name (in any format eg proper, lower etc) in column A, it returns the number of legs in column B.
I know I can do this job using index and match, however how do tell the formula to ignore something when someone puts a word or leaves the cell blank.
Hi,
Just use
=INDEX(B:B, MATCH(F4, A:A,0))
The formula is non-case sensitive, it will take care any form like below
dOg
CAT
SnaKE
HuMAN
unless you requirement is more than that then you can use sumproduct with index match for multiple conditions
Christopher Yap
I did, but when ever I come across a word not in my database, it prints NA which i don't want.Originally Posted by bluesky63
Hi,
the use IFERROR(INDEX(B:B, MATCH(F4, A:A,0)), "Not found"), or whatever message that you display
or you can attach a more comprehensive datasets that will show these error for me to take a look
I don't want it to do anything on that cell. So having a string like "not found" or "" is not what i want. I want it to no do anything or skip it.
Cell B3 formula , drag down
Formula:
Please Login or Register to view this content.
Last edited by wk9128; 02-04-2022 at 05:17 AM.
this worked, can this be done using index match?
If someone mis-spells cat as cyat... you'll need an entirely different approach (Microsoft fuzzy lookup, free from their www). But if you just want to ensure that "tiny cat" is seens as being "cat" and returns 4, then use:
=IFERROR(LOOKUP(1000,SEARCH($F$4:$F$7,A3),$G$4:$G$7),"")
Make a note to explain the exceptions and real requirements right fromt he start.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Noted!
Does search take in to account variations of the words?
Last edited by AliGW; 02-04-2022 at 05:13 AM. Reason: PLEASE don't quote unnecessarily!
ANS. post #7
index + match
Try this formula
Formula:Please Login or Register to view this content.
Last edited by wk9128; 02-04-2022 at 05:18 AM.
wkd9128. Does your formula work for "tiny cat" in column A? It doesn't.
Bob, what do you mean by "variations of the words"? My formula will work with "tiny cat", or "cat-creature", but will not work with "cyat". You need to define the degree of variability/sloppy data entry that can happen.
Variation such as any word that has the word cat eg categories.
Last edited by AliGW; 02-04-2022 at 05:14 AM. Reason: PLEASE don't quote unnecessarily!
@glenn what is the purpose of the 1000 in your code?
Please Login or Register to view this content.
It's just a big number that needs to be larger than the position of the first character of the keyword (cat) in the search string (tiny cat). In this case cat begins at position 6 of the string "tiny cat". As long as the number is > than the largest value, it works. So... 1000 is simply a big number, larger than your largest string. 1000, 10^6, 10^9... it does not matter at all. Just needs to be big.
I understand, thank you for the clear explanation.
@Glenn Kennedy Thank you POST #6 AND POST #9 The formula has been modified
@bob112233 You're Welcome. Glad to help . Thank You for the feedback
If you finally get a solution please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".
- Please click on the *Add Reputation button at the bottom left. Whoever helps you, give it to whoever you like
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks