Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

hello, I need a formula that tells worksheet A "hey if you find the same numbers you have in column y and find it somewhere in Worksheet B, RowA columns 1-187, then I want you to put the inverse number (i.e. if positive then negative number) found in RowH of Worsheet B in Row Z of worksheet A"
Solved by D. D. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 24/08/2018 - 10:33
Welcome to ExcelChat!
User 24/08/2018 - 10:33
hello
Excelchat Expert 24/08/2018 - 10:33
Will you provide some data which we will work with?
User 24/08/2018 - 10:34
I do not have date. but I can make something up if that will help
User 24/08/2018 - 10:34
hello, I need a formula that tells worksheet A "hey if you find the same numbers you have in column y and find it somewhere in Worksheet B, RowA columns 1-187, then I want you to put the inverse number (i.e. if positive then negative number) found in RowH of Worsheet B in Row Z of worksheet A"
User 24/08/2018 - 10:34
So imagine you have two excel workbook open and on workbook is saying to another workbook the message above
Excelchat Expert 24/08/2018 - 10:37
Okay, so if column Y has numbers which also exist in column A from second sheet, they you need to put the number from column H on Sheet2 and column Z on Sheet1?
Excelchat Expert 24/08/2018 - 10:39
Yes.. it would be better to show on the example
User 24/08/2018 - 10:40
ok so in the example I just created I want C3-E3 to come up with the following numbers -10, -9, 21
Excelchat Expert 24/08/2018 - 10:40
Okay, I got your problem, will work on it
User 24/08/2018 - 10:42
so how can I create a formula that says hey sheet 1 if you find in sheet 2 the numbers you have in C2-E2 in sheet2 column A then I want you to put the values in sheet 2 column B (inverse) in cloume 3
User 24/08/2018 - 10:43
ok but how do I drag that formula to the rest?
Excelchat Expert 24/08/2018 - 10:44
Excelchat Expert 24/08/2018 - 10:44
=iferror(vlookup(left(C2,3)*1,Sheet2!\$A:\$B,2,0)*-1,"")
Excelchat Expert 24/08/2018 - 10:44
it tries to find the number in the column
Excelchat Expert 24/08/2018 - 10:44
if it finds, it multiplies it by -1 and take to the cell
Excelchat Expert 24/08/2018 - 10:44
if not, we leave the empty space
User 24/08/2018 - 10:45
ok can this work between workbooks instead of worksheets?
Excelchat Expert 24/08/2018 - 10:45
Yes, you will just have the references to the books instead of sheets
User 24/08/2018 - 10:46
So can you explain part by part of this formula
Excelchat Expert 24/08/2018 - 10:46
Sure
Excelchat Expert 24/08/2018 - 10:46
First we take first 3 symbols from the upper cell (row 2)
Excelchat Expert 24/08/2018 - 10:46
using the Left function
Excelchat Expert 24/08/2018 - 10:47
Then we try to find this number on Sheet2, using the vlookup function
Excelchat Expert 24/08/2018 - 10:47
If vlookup doesn't find a value, it gives an error
Excelchat Expert 24/08/2018 - 10:47
so in this case we just leave the cell blank
Excelchat Expert 24/08/2018 - 10:47
as a result, we have a formula which returns the required number if condition is met
Excelchat Expert 24/08/2018 - 10:48
iferror function allowed us to leave an empty space instead of error
Excelchat Expert 24/08/2018 - 10:49
Did you understand how it works?
User 24/08/2018 - 10:50
I'm trying to see if I get it
Excelchat Expert 24/08/2018 - 10:50
left(D2,3) -> we take number from the text
Excelchat Expert 24/08/2018 - 10:50
vlookup() - we search that number on sheet2
User 24/08/2018 - 10:51
Why is it (c2,3)
Excelchat Expert 24/08/2018 - 10:51
iferror() - if we didn't find the numbe, we leave the blank space
Excelchat Expert 24/08/2018 - 10:51
Because we take first 3 characters from that cell
User 24/08/2018 - 10:52
then why times 1? after that?
Excelchat Expert 24/08/2018 - 10:52
it converts the text to a number
Excelchat Expert 24/08/2018 - 10:52
First it was just part of the text, but when we multiply it by 1, it becomes a number (Excel logic)
User 24/08/2018 - 10:54
what does \$A:\$B mean?
Excelchat Expert 24/08/2018 - 10:55
it mean column A and B from Sheet2
Excelchat Expert 24/08/2018 - 10:55
\$ signs freezes the column values when we extend the formula
User 24/08/2018 - 10:55
ok but then why did you put 2 after that?
Excelchat Expert 24/08/2018 - 10:56
This is the vlookup function, which has 4 arguments
Excelchat Expert 24/08/2018 - 10:56
What we search (we found numbers using the left function)
Excelchat Expert 24/08/2018 - 10:56
Where we search (Column A and B) from Sheet2
Excelchat Expert 24/08/2018 - 10:56
The number of column which has the result
Excelchat Expert 24/08/2018 - 10:56
In our case it is the second column
Excelchat Expert 24/08/2018 - 10:57
And then the parameter which tells the type of vlookup (in our case we need exact match, so we put 0)
Excelchat Expert 24/08/2018 - 10:57
Have you worked with functions before?
Excelchat Expert 24/08/2018 - 10:57
Because if you haven't, this formula might be complicated for you
User 24/08/2018 - 10:59
what are the " " at the end
Excelchat Expert 24/08/2018 - 10:59
this is part of the iferror formula which we used
Excelchat Expert 24/08/2018 - 10:59
First we type our main result (vlook up result from Sheet2)
Excelchat Expert 24/08/2018 - 10:59
Then we type what to return if the cell has error
Excelchat Expert 24/08/2018 - 11:00
"" - mean nothing, empty space
Excelchat Expert 24/08/2018 - 11:00
So if vlookup couldn't find the number in Sheet2, it will return an error. Instead of showing this error, we show nothing - empty cell
Excelchat Expert 24/08/2018 - 11:06
Do you have other questions?
Excelchat Expert 24/08/2018 - 11:11
Excelchat Expert 24/08/2018 - 11:11
The main formula is =iferror(vlookup(left(C2,3)*1,Sheet2!\$A:\$B,2,0)*-1,"")

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.