Excel - IF Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.