vlookup

[Andy128]

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

 

[Yogi Anand]

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.

 

[Andy128]

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

 

[Yogi Anand]

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.

 

[dimbasco]

Andy,

Seems simple to me.. Am I missing something? for the 1st part Try this:

Book1
	B	C	D	E
1		Months	Class	Salary
2		1	X	$40,000
3		13	Y	$60,000
4		25	Z	$80,000
5
6	Name	Months	Class	Salary
7	David	40	Z	$80,000
8	Lacey	21	Y	$60,000
9	Sam	27	Z	$80,000
10	Charles	10	X	$40,000
11				$260,000
Sheet1

For Part 2: (I'm a little confused what you want.. is it this or similar?)

Book1
	B	C	D	E	F	G
1		Months	Class	Salary
2		1	X	$40,000		SpecialValueforDavid=$80000
3		13	Y	$60,000		$100,000
4		25	Z	$80,000
5
6	Name	Months	Class	Salary
7	David	40	Z	$80,000	100000
8	Lacey	21	Y	$60,000	NotDavid
9	Sam	27	Z	$80,000	NotDavid
10	Charles	10	X	$40,000	NotDavid
11				$260,000
Sheet1

 

[Andy128]

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

 

[dimbasco]

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?

I could help if you want to email me your spreadsheet (Please add comment boxes with what you want to do) at dimbasco at hotmail dot com

 

[arelh]

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

 

[Andy128]

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

Keith