# vlookup

#### Andy128

##### Board Regular
Have used a vlookup formula to assign entry in column of cells -salary for each employee. When I try to add the values in the cells- it won't do it. Therefore, I can not add up all the employees wages to get a total.
Is this a product of the vlookup?

Also, in this example can I substitute "\$40,000" to a value in a specific cell like "A15" ?

=vlookup(a1:a7,{1,"A15"},2) * where A15 is the value in cell A15

Thanks- Andy

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Andy128 said:
Have used a vlookup formula to assign entry in column of cells -salary for each employee. When I try to add the values in the cells- it won't do it. Therefore, I can not add up all the employees wages to get a total.
Is this a product of the vlookup?

Also, in this example can I substitute "\$40,000" to a value in a specific cell like "A15" ?

=vlookup(a1:a7,{1,"A15"},2) * where A15 is the value in cell A15

Thanks- Andy
Hi Andy:

It is not clear what you are trying to accomplish here. Your VLOOKUP formulation doesn't look good either. What exactly are you trying to do -- please first describe what you are trying to do clearly in words without resorting to any formulas.

And did you not have another post related to the PayRoll issue -- if so I suggest you post additional questions regarding that topic in the same thread.

Yogi Anand- I have posted back on the same thread but am leary as to if it will be answered as I had already thanked the guys for solving the problem only to discover yet another problem with in the formula. Anyway- here is a brief explanation of the problem now;

The formula worked great and put the numbers exactly where I needed and in the manner I needed. But- apparently when you assign a number in the " " marks- it carries no value other than text- therefore, I cannot add up the columns. For example;

if I look up A1 and tell it to assign "\$40,000" if it matches a specified criteria- it holds no value that I can add. When I try to sum up the column where I have applied the vlookup formula- it simply returns \$0.00.

Is there a way to have it hold a value?

And to the second half of the question- If I look up A1 and it meets the criteria I set- can I have it assign the value within another cell? Example- look in cell A1 and if the value is "David"- then put the value contained in cell A15 in the cell next to A1. Does that make sense?

Andy

Andy128 said:
Yogi Anand- I have posted back on the same thread but am leary as to if it will be answered as I had already thanked the guys for solving the problem only to discover yet another problem with in the formula. Anyway- here is a brief explanation of the problem now;

The formula worked great and put the numbers exactly where I needed and in the manner I needed. But- apparently when you assign a number in the " " marks- it carries no value other than text- therefore, I cannot add up the columns. For example;

if I look up A1 and tell it to assign "\$40,000" if it matches a specified criteria- it holds no value that I can add. When I try to sum up the column where I have applied the vlookup formula- it simply returns \$0.00.

Is there a way to have it hold a value?

And to the second half of the question- If I look up A1 and it meets the criteria I set- can I have it assign the value within another cell? Example- look in cell A1 and if the value is "David"- then put the value contained in cell A15 in the cell next to A1. Does that make sense?

Andy
Hi Andy:

It is nice of you to have thanked the guys who helped you. You see the topic does not get closed even if the poster's question gets answered -- because some one else may come back and post a different, a better, or an alternate solution, the original poster may come back with additional questions related to the same topic, and so on.

Now as in this case, I had the opportunity to look at the question in your orighinal thread, and I have posted a response there -- so please go back there and have a look and see if that answers your question -- if not post back in the same thread with more details.

Andy,

Seems simple to me.. Am I missing something? for the 1st part Try this:
Book1
BCDE
1MonthsClassSalary
21X\$40,000
313Y\$60,000
425Z\$80,000
5
6NameMonthsClassSalary
7David40Z\$80,000
8Lacey21Y\$60,000
9Sam27Z\$80,000
10Charles10X\$40,000
11\$260,000
Sheet1

For Part 2: (I'm a little confused what you want.. is it this or similar?)
Book1
BCDEFG
1MonthsClassSalary
21X\$40,000SpecialValueforDavid=\$80000
313Y\$60,000\$100,000
425Z\$80,000
5
6NameMonthsClassSalary
7David40Z\$80,000100000
8Lacey21Y\$60,000NotDavid
9Sam27Z\$80,000NotDavid
10Charles10X\$40,000NotDavid
11\$260,000
Sheet1

kunal-
It appears that the if formula is limiting to 7 functions or classes if you will. Vlookup, as I understand it, will allow me to set a specific criteria to a broader range and not over taxing the if formula. However- I am very new to this and you probably know tricks to make it work. It will take me some time to digest your formula. I will try it and get back with you. Thanks.
Andy

Hi Andy,

I havent come across a limitation in number of functions/ classes in Excel with Vlookup yet.. I use Vlookup extensively.
What you need to do is try the example given within Excel Help for Vlookup (as recommended by Yogi). It is very helpful.
Can you download Colo's HTMLMAKER or HTMLMAKER Lite (from this website) and post a picture of the code here?

Andy whenever you encase the salary or the cell reference in Quotes("")the result is treated as text- i believe - if you have a range of values in a sheet that comprise the values you require it is simpler to create a name for that range and refer to the range rather than individual values - for example if your salary details are in cells d1 - f10 select those cells and go to the insert menu select name - define and call it salary - then in your lookup you can say =vlookup(A1,salary,3,false) where A1 is the value you want to find in the first column of the range - salary is the range of values - and 3 is the column number in the range of the value required

Kunal,
Am I mistaken. The example above that you gave is an IF statment.

Keith

Replies
17
Views
604
Replies
0
Views
285
Replies
1
Views
1K
Replies
1
Views
416
Replies
4
Views
753

1,203,071
Messages
6,053,369
Members
444,658
Latest member
lhollingsworth

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back