Question description:
This user has given permission to use the problem statement for this
blog.
I am trying to run a formula that takes a value, and if it appears in both columns on a separate tab, it pulls the difference between the adjacent cells value. I have tried if, match, and vlookup formulas but can't seem to figure it out. Any help is much much appreciated.
Below is small example of what I am trying to run asking if CA9099 it should tell me $1.
So in a cell I put =ifmatch(CA9099, it will take the difference, and if the value doesn't exist in column A on the tab it reads N/A
A B
CA9099 $21 CA9099 $20
CA5674 $20 CA8788 $30
CA8788 $35 CA5674 $15
Solved by S. F. in 39 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
26/03/2018 - 03:30
I am not sure
Excelchat Expert
26/03/2018 - 03:30
Hello
User
26/03/2018 - 03:30
HI
Excelchat Expert
26/03/2018 - 03:30
Welcome to Excelchat
Excelchat Expert
26/03/2018 - 03:30
Lets look at your problem, and find a sutable solution
Excelchat Expert
26/03/2018 - 03:31
What exactly is the issue?
User
26/03/2018 - 03:32
So for row C I am trying to run a statement asking if i take the value in column B, if it matches to column B on the "Data" page to subtract Columns B from E on the "Data" Page as a result
User
26/03/2018 - 03:33
does that make sense
Excelchat Expert
26/03/2018 - 03:33
You are saying
Excelchat Expert
26/03/2018 - 03:33
that
Excelchat Expert
26/03/2018 - 03:34
if there is a value under column c in sheet 1
Excelchat Expert
26/03/2018 - 03:34
then what?
Excelchat Expert
26/03/2018 - 03:35
Column B and E are row labels and class code respectively on the "data" sheet
User
26/03/2018 - 03:35
example
Excelchat Expert
26/03/2018 - 03:35
yeah?
User
26/03/2018 - 03:36
if AL5437 is in column "B" on "Data" then take the difference of AL5437 adjacent cell in column "C" from AL5437 adjacent cell in column "G"
User
26/03/2018 - 03:37
I want to ask if x value is in a column, can i an adjacent amount associated with this X value and find the other exact X value in a different column and subtract the 2
Excelchat Expert
26/03/2018 - 03:37
ohhk
Excelchat Expert
26/03/2018 - 03:39
you want is, in sheet1, the class code should match with the 2 columns in data sheet and the difference of the two should be reflected next to class code column in sheet1
User
26/03/2018 - 03:39
So next to AL5437 it should say $0 because on the Data sheet that value exists in both columns and is the same amount
Excelchat Expert
26/03/2018 - 03:39
isnt it?
User
26/03/2018 - 03:39
yes correct
Excelchat Expert
26/03/2018 - 03:39
Ok let me just see
User
26/03/2018 - 03:41
that can't be it because it should read $0
Excelchat Expert
26/03/2018 - 03:41
wait
User
26/03/2018 - 03:42
still not quite right because that just pulls the value
Excelchat Expert
26/03/2018 - 03:42
yeah
User
26/03/2018 - 03:43
If that value matches the first column I want take the adjacent value from the other column and get the difference
Excelchat Expert
26/03/2018 - 03:43
I just need to match the cost now
Excelchat Expert
26/03/2018 - 03:46
We wll ahve to use vlookup
Excelchat Expert
26/03/2018 - 03:46
have*
User
26/03/2018 - 03:47
how should we go about doing that
Excelchat Expert
26/03/2018 - 03:47
its like
Excelchat Expert
26/03/2018 - 03:47
explain
Excelchat Expert
26/03/2018 - 03:48
If the class code matches with column B, it should return the adjacent value, and similarly for column E too
User
26/03/2018 - 03:48
yes
User
26/03/2018 - 03:49
it returns the difference can we subtract the two
Excelchat Expert
26/03/2018 - 03:49
yes exactly
User
26/03/2018 - 03:49
WOOOO
Excelchat Expert
26/03/2018 - 03:49
You are pretty smart
Excelchat Expert
26/03/2018 - 03:49
:)
User
26/03/2018 - 03:49
no you are
Excelchat Expert
26/03/2018 - 03:49
Now, lets just do it
User
26/03/2018 - 03:51
okay how
Excelchat Expert
26/03/2018 - 03:51
lets try
User
26/03/2018 - 03:53
it would just be =if((vlookup(b5),table array column C,value if true, value if false)-(vlookup(b5),table array Column G ,value if true, value if false))
Excelchat Expert
26/03/2018 - 03:54
ok
User
26/03/2018 - 03:54
could we just run 2 different v lookups in one formula and make the formula punch out the adjacent amount and it will subtract the two
User
26/03/2018 - 03:56
is what im saying making sense/ is it possible?
Excelchat Expert
26/03/2018 - 03:56
just wait
Excelchat Expert
26/03/2018 - 03:57
Check
Excelchat Expert
26/03/2018 - 03:57
)
Excelchat Expert
26/03/2018 - 03:57
:)
Excelchat Expert
26/03/2018 - 03:57
You are smart buddy :)
Excelchat Expert
26/03/2018 - 03:57
Should I explain you what I did?
User
26/03/2018 - 03:57
no youre smart
Excelchat Expert
26/03/2018 - 03:58
You are very humble mate
User
26/03/2018 - 03:58
just not sure what the 2 and 3
Excelchat Expert
26/03/2018 - 03:58
2,3 repersnts the column to retuen the value
Excelchat Expert
26/03/2018 - 03:58
in vlookup
Excelchat Expert
26/03/2018 - 03:59
1st value is the value we are looking for, 2nd is the range in which we want to see. 1st vllokup formula had 2 columns, and we wanted a return from 2nd column. In 2nd vlookup, we had 3 columns
Excelchat Expert
26/03/2018 - 04:00
Hence the return from 3rd column
Excelchat Expert
26/03/2018 - 04:00
Are we done mate?
User
26/03/2018 - 04:00
so you have to hightlight the first and last cell you want to search for
User
26/03/2018 - 04:01
and absolute reference them
Excelchat Expert
26/03/2018 - 04:01
Exactly
Excelchat Expert
26/03/2018 - 04:01
If our range has 5 columns, and we want the return value from 4th column, just enter 4
User
26/03/2018 - 04:02
gotcha gotcah
User
26/03/2018 - 04:02
thank you so much again
Excelchat Expert
26/03/2018 - 04:02
Sure :)
Excelchat Expert
26/03/2018 - 04:02
Just give me a good rating :D
User
26/03/2018 - 04:02
wait on last thing
User
26/03/2018 - 04:03
in this formula if a value is in the first column and not the second can we make it read like N/A or no match
Excelchat Expert
26/03/2018 - 04:03
yes?
Excelchat Expert
26/03/2018 - 04:04
I don't think so
Excelchat Expert
26/03/2018 - 04:05
There may be a way, but it will take more time. Ill ask you to try this as another question
User
26/03/2018 - 04:05
no thats okay
User
26/03/2018 - 04:05
thank you again
User
26/03/2018 - 04:05
so much help
Excelchat Expert
26/03/2018 - 04:05
The pleasure is all mine
Excelchat Expert
26/03/2018 - 04:05
ake care
Excelchat Expert
26/03/2018 - 04:05
Take care mate
User
26/03/2018 - 04:07
are you still there
Excelchat Expert
26/03/2018 - 04:07
Yes
User
26/03/2018 - 04:08
what does the data!C81, 0 represent
Excelchat Expert
26/03/2018 - 04:08
Please mark it as done
User
26/03/2018 - 04:08
thats the one thing confusing me
Excelchat Expert
26/03/2018 - 04:09
=(VLOOKUP(B5,Data!$B$3:$C$95,2))-(VLOOKUP(B5,Data!$E$3:$G$83,3))
Excelchat Expert
26/03/2018 - 04:09
where?
User
26/03/2018 - 04:09
nvm will mark as done ty
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.
