Question description:
This user has given permission to use the problem statement for this
blog.
I need to input a formula into Sheet1, that searches for a value in Sheet2 (C5:C56) that is present in Sheet1 (defined cell, C5), and then if "true", enter the value that's in the same row as the value has been found in Sheet2 but from column E.
e.g. sheet1 the set value is A. Search Sheet to C5:C56 for "A" and if found (say C35) then enter cell value of E35
Solved by Z. S. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
06/07/2018 - 09:52
Hello, I hope you are having a good day. I am here to help with your question.
Excelchat Expert
06/07/2018 - 09:53
Would you have a sample data to work with or would you like me to demonstrate with example data.
Excelchat Expert
06/07/2018 - 09:55
In the interest of time let me demonstrate with an example data
Excelchat Expert
06/07/2018 - 09:57
Are you with me?
Excelchat Expert
06/07/2018 - 09:57
Let's say we are looking for values in Column A
Excelchat Expert
06/07/2018 - 09:59
To match with expectations I am using same columns as your question in Sheet 2
Excelchat Expert
06/07/2018 - 10:02
I have built the formula with comments for your reference
Excelchat Expert
06/07/2018 - 10:03
if you are experiencing difficulties I will leave formulas here as well
Excelchat Expert
06/07/2018 - 10:04
check if A3 in sheet 1 is present in Sheet 2 Range C4 to C10 (You can change this to any range you want)
Excelchat Expert
06/07/2018 - 10:04
=Isnumber(MATCH(A3,Sheet2!$C$4:$C$10,0))
Excelchat Expert
06/07/2018 - 10:04
this works perfectly if it is numerical values
Excelchat Expert
06/07/2018 - 10:05
in case of text the best one would be =Istext(VLOOKUP(A3,Sheet2!$C$4:$C$10,1,0))
Excelchat Expert
06/07/2018 - 10:05
this is to avoid any potential errors with Match
Excelchat Expert
06/07/2018 - 10:05
now if this is true then show values in column E or else show "Not Found"
Excelchat Expert
06/07/2018 - 10:06
=IF(B3,VLOOKUP(A3,Sheet2!$C$5:$E$10,3,FALSE),"Not Found")
Excelchat Expert
06/07/2018 - 10:06
not you can have it all in one formula
Excelchat Expert
06/07/2018 - 10:06
like this =IF(B3,VLOOKUP(Isnumber(MATCH(A3,Sheet2!$C$4:$C$10,0)),Sheet2!$C$5:$E$10,3,FALSE),"Not Found")
Excelchat Expert
06/07/2018 - 10:07
but for the purpose of demonstration, I split them into separate columns
Excelchat Expert
06/07/2018 - 10:07
I hope I answered your question satisfactorily. I would love to receive your feedback.
Excelchat Expert
06/07/2018 - 10:07
Have a fantastic day :)
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.
