All solutions FormulaVLOOKUP Expert Solution – How to Use a VLOOKUP Formula

Excel - How to Use a VLOOKUP Formula - Expert Solution

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.

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.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc