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
Here is your result
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
If you need to save the file, please press File -> download as -> xlsx
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.
