Question description:
This user has given permission to use the problem statement for this
blog.
I'm trying to make a formula for if a number in a particular cell (f2 for example) is present anywhere in column D, I want to pull back a true or false. I need to recreate this for every cell in column F to match up all existing numbers
Solved by A. A. in 15 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
19/06/2018 - 09:15
Hi…Welcome to Got It Pro
Excelchat Expert
19/06/2018 - 09:16
Can you do an illustration of the data and likely result that you want?
User
19/06/2018 - 09:16
sure
Excelchat Expert
19/06/2018 - 09:16
Thanks...that will be of great help.
User
19/06/2018 - 09:17
I want to take cell b1 and use a formula to search all of column A to see if the value exists and pull back a true or false
Excelchat Expert
19/06/2018 - 09:18
ok..we can do a lookup formula.
Excelchat Expert
19/06/2018 - 09:18
Let me create the formula in column C
Excelchat Expert
19/06/2018 - 09:20
So the formula is ready in C1 and down
Excelchat Expert
19/06/2018 - 09:21
Now..there is no data matching, if it finds a match, then it will return True
Excelchat Expert
19/06/2018 - 09:21
You can test it by pasting any of column B value in A
User
19/06/2018 - 09:21
it worked perfectly! Is there any chance you mind explaining how the formula works? I've had trouble with this since I have to do this every month with company data, and I've always barely scraped by with some shoddy formulas
Excelchat Expert
19/06/2018 - 09:21
Sure..I will be glad to explain
Excelchat Expert
19/06/2018 - 09:22
This is a Match function.
Excelchat Expert
19/06/2018 - 09:23
match(B1,A:A,0) ---- Match B1 in column A and 0 stands for exact match
Excelchat Expert
19/06/2018 - 09:23
Syntax is Match(Lookupvalue, lookup array, match type)
Excelchat Expert
19/06/2018 - 09:24
This will return the row number if a match is found.
Excelchat Expert
19/06/2018 - 09:24
Let me copy B1 value in A5 as an example
Excelchat Expert
19/06/2018 - 09:25
Now the match function will return value as 5 as the B1 value exist in row 5
Excelchat Expert
19/06/2018 - 09:26
isnumber( before Match is checking if indeed the match function has return a numeric value which means the value exist in column A.
Excelchat Expert
19/06/2018 - 09:27
if( before Isnumber is an If statement which test whether match function return a numeric value, if so, result should say "Yes", otherwise say "No"
Excelchat Expert
19/06/2018 - 09:28
Finally, Iferror at the beginning and ,"False") at the end is an error handling function.
Excelchat Expert
19/06/2018 - 09:28
If there are no match found, the formula will result in error, so its capturing and saying False if no match found.
Excelchat Expert
19/06/2018 - 09:28
And that's it...Does this explanation help?
User
19/06/2018 - 09:29
Very much so, I appreciate the meticulous detail because these formulas are so confusing at first glance
User
19/06/2018 - 09:29
I appreciate the help! This will shave hours off of my work every month
Excelchat Expert
19/06/2018 - 09:29
Yes...this are considered advanced lookup formulas :)
Excelchat Expert
19/06/2018 - 09:29
Anything else I may assist with?
User
19/06/2018 - 09:29
Nope, you solved it all, thank you so much
Excelchat Expert
19/06/2018 - 09:30
Perfect...Please do give me a good feedback for my service.
User
19/06/2018 - 09:30
of course, should I hit end session?
Excelchat Expert
19/06/2018 - 09:30
Thanks for your time. Please do come back for any new question. Have a great evening!
Excelchat Expert
19/06/2018 - 09:30
Yes, you can end the session now. Thanks!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
