anyone good with excel?
12-19-2011, 06:21 PM
#1
Contributors
Thread Starter
Join Date: Jul 2009
Location: Toms River NJ
Posts: 611
Likes: 0
Received 0 Likes
on
0 Posts
My Ride: 2013 535i xDrive AW/Black M-kit
i have a big project to do for my appraisal company and i cant figure out how to do this comparison in excel. i set up an example of what i need to do in a very simple form. the actual tables i need too do this too are much much larger. if anyone can explain to me what function would do this and how to use it id really appreciate it. im hoping theres an excel function and i dont have to use some VB coding. any help greatly appreciated. deadline approaching. 
it wont let me upload an excel file so here is a screen shot
it wont let me upload an excel file so here is a screen shot
12-19-2011, 07:03 PM
#4
Contributors
Thread Starter
Join Date: Jul 2009
Location: Toms River NJ
Posts: 611
Likes: 0
Received 0 Likes
on
0 Posts
My Ride: 2013 535i xDrive AW/Black M-kit
12-20-2011, 12:30 PM
#6
Contributors
Join Date: Nov 2004
Location: NYC & LI
Posts: 2,460
Likes: 0
Received 0 Likes
on
0 Posts
if what you are trying to do is match a common variable and pull data from a separate worksheet based on that value, then yes, as noted above, you can easily use a vlookup function to accomplish this task.
So , to accomplish this merge, you would need to use a formula such as this: =VLOOKUP(A3,Sheet2!A:B,2,FALSE)
Now I?ll break down the formula in parenthesis:
This statement says that the value(s) that you are using as the control are in Column A ? where your first value is in A3.
Now, assuming you have the second set of data on a separate worksheet (such as Sheet2), you would place that data in columns A and B so that when you are ?looking? for it on the first sheet(your merged sheet) you will pull the proper data set(This relates to the Sheet2!A:
. Next, you are telling the compute what to do when you find the match, which relates to the 2 in the formula, and it just means that when you find the value match, move two to the right ? the value you are matching is always 1, so to pull data in a column next to the match, you would enter a 2 and so on?Finally, the FALSE command says that if you don?t find a match, return a value of N/A.
Of course, to cheat a bit, go back to your example and take out the value you have listed in Column J14, which shows 12.25.
After you delete that value, enter this into the cell: =VLOOKUP(I14,A3:B11,2,FALSE). You should see your value reappear. You can then copy and paste this down the column to auto populate your values.
Give it a try and then you can modify as required based on how I describe above...good luck
So , to accomplish this merge, you would need to use a formula such as this: =VLOOKUP(A3,Sheet2!A:B,2,FALSE)
Now I?ll break down the formula in parenthesis:
This statement says that the value(s) that you are using as the control are in Column A ? where your first value is in A3.
Now, assuming you have the second set of data on a separate worksheet (such as Sheet2), you would place that data in columns A and B so that when you are ?looking? for it on the first sheet(your merged sheet) you will pull the proper data set(This relates to the Sheet2!A:
. Next, you are telling the compute what to do when you find the match, which relates to the 2 in the formula, and it just means that when you find the value match, move two to the right ? the value you are matching is always 1, so to pull data in a column next to the match, you would enter a 2 and so on?Finally, the FALSE command says that if you don?t find a match, return a value of N/A.Of course, to cheat a bit, go back to your example and take out the value you have listed in Column J14, which shows 12.25.
After you delete that value, enter this into the cell: =VLOOKUP(I14,A3:B11,2,FALSE). You should see your value reappear. You can then copy and paste this down the column to auto populate your values.
Give it a try and then you can modify as required based on how I describe above...good luck
12-20-2011, 03:15 PM
#8
Contributors
Thread Starter
Join Date: Jul 2009
Location: Toms River NJ
Posts: 611
Likes: 0
Received 0 Likes
on
0 Posts
My Ride: 2013 535i xDrive AW/Black M-kit
if what you are trying to do is match a common variable and pull data from a separate worksheet based on that value, then yes, as noted above, you can easily use a vlookup function to accomplish this task.
So , to accomplish this merge, you would need to use a formula such as this: =VLOOKUP(A3,Sheet2!A:B,2,FALSE)
Now I?ll break down the formula in parenthesis:
This statement says that the value(s) that you are using as the control are in Column A ? where your first value is in A3.
Now, assuming you have the second set of data on a separate worksheet (such as Sheet2), you would place that data in columns A and B so that when you are ?looking? for it on the first sheet(your merged sheet) you will pull the proper data set(This relates to the Sheet2!A:. Next, you are telling the compute what to do when you find the match, which relates to the 2 in the formula, and it just means that when you find the value match, move two to the right ? the value you are matching is always 1, so to pull data in a column next to the match, you would enter a 2 and so on?Finally, the FALSE command says that if you don?t find a match, return a value of N/A.
Of course, to cheat a bit, go back to your example and take out the value you have listed in Column J14, which shows 12.25.
After you delete that value, enter this into the cell: =VLOOKUP(I14,A3:B11,2,FALSE). You should see your value reappear. You can then copy and paste this down the column to auto populate your values.
Give it a try and then you can modify as required based on how I describe above...good luck
So , to accomplish this merge, you would need to use a formula such as this: =VLOOKUP(A3,Sheet2!A:B,2,FALSE)
Now I?ll break down the formula in parenthesis:
This statement says that the value(s) that you are using as the control are in Column A ? where your first value is in A3.
Now, assuming you have the second set of data on a separate worksheet (such as Sheet2), you would place that data in columns A and B so that when you are ?looking? for it on the first sheet(your merged sheet) you will pull the proper data set(This relates to the Sheet2!A:
. Next, you are telling the compute what to do when you find the match, which relates to the 2 in the formula, and it just means that when you find the value match, move two to the right ? the value you are matching is always 1, so to pull data in a column next to the match, you would enter a 2 and so on?Finally, the FALSE command says that if you don?t find a match, return a value of N/A.Of course, to cheat a bit, go back to your example and take out the value you have listed in Column J14, which shows 12.25.
After you delete that value, enter this into the cell: =VLOOKUP(I14,A3:B11,2,FALSE). You should see your value reappear. You can then copy and paste this down the column to auto populate your values.
Give it a try and then you can modify as required based on how I describe above...good luck
ok just tried it, i think this is exactly what im looking for. one issue i had while trying it. when i coppied the formula =VLOOKUP(I13,A3:B11,2,FALSE) into J13 it worked then when i tried to autofill the rest of the J column J14-J24 the array reference part of the formula (A3:B11) changed in each row to (A4:B12) then (A5:B13) which is no good because that puts you outside the table array. alternativly if i copy and paste the exact same formula into J13-J24 the lookup value stays constant which is ging to duplicate the same result for each row. is there a way i can autofill column J with the previous mentioned formula but keep the table array portion (A3:B11) of the function the same?
EDIT ******************* SOLVED MY OWN PROBLEM***************************
google search showed that $A$3:$B$11 will keep it constant... man excel is pretty intense hahah thanks again m630 and acuteperformance
Thread
Thread Starter
Forum
Replies
Last Post