**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.*