Adding to VLOOKUP

[Acody]

I am using the VLOOKUP Formula to add numbers together (from two different tables)only if their job number matches. If the job numbers do not match I want my cell to read the total from my second table. How can I do this?
Thanks,
Annie

 

[Aladin Akyurek]

On 2002-03-15 16:19, Acody wrote:
I am using the VLOOKUP Formula to add numbers together (from two different tables)only if their job number matches. If the job numbers do not match I want my cell to read the total from my second table. How can I do this?
Thanks,
Annie

Can you post the VLOOKUP formula that you use along with where the job numbers are to be found?

Aladin

 

[Acody]

=SUM(K31+VLOOKUP(B31,jobnumber,10,FALSE))

1st Table: (3 Job #s)
B14:B16

2nd Table: (3 Job 3s)-only one same # as first table
B30:B32

Thanks!

 

[Anne Troy]

=if(b31=VLOOKUP(B31,jobnumber,1,FALSE),SUM(K31+VLOOKUP(B31,jobnumber,10,FALSE)),"Not Equal")

I think...

 

[Aladin Akyurek]

On 2002-03-15 16:35, Acody wrote:
=SUM(K31+VLOOKUP(B31,jobnumber,10,FALSE))

1st Table: (3 Job #s)
B14:B16

2nd Table: (3 Job 3s)-only one same # as first table
B30:B32

Thanks!

Annie,

I'm still confused.

'jobnumber' seems to be a lookup table. What range is it? And how B14:B16 and B30:B32 are related to 'jubnumber'?

Does K31 hold a value that is computed by a VLOOKUP formula?

Aladin

 

[Acody]

Aladin, Thanks for your continued help : )

The range for "jobnumber" is:
B9 to K9
B16 to K16
(everything in the square above).

B14:B16 are the job numbers from week one that I have on my timesheet.
B30:B32 are the job numbers from my second week, and on the second week I added one new job that I did not work on the first week.
So when I plug in my formula for the new job my cell reads N/A...b/c my formula wants to pull a number from week #1.
I want my formula to look to week one for a similiar job number, if it does not find one, then add only the hours worked on that job for week two.
K31 it a sum of the hours I worked on that job for week two. K31 is the cell I want my formula to refer to if week one does not have the same job number as week two.

Sorry that this is so confusing! : )

 

[Aladin Akyurek]

On 2002-03-19 08:31, Acody wrote:
Aladin, Thanks for your continued help : )

The range for "jobnumber" is:
B9 to K9
B16 to K16
(everything in the square above).

B14:B16 are the job numbers from week one that I have on my timesheet.
B30:B32 are the job numbers from my second week, and on the second week I added one new job that I did not work on the first week.
So when I plug in my formula for the new job my cell reads N/A...b/c my formula wants to pull a number from week #1.
I want my formula to look to week one for a similiar job number, if it does not find one, then add only the hours worked on that job for week two.
K31 it a sum of the hours I worked on that job for week two. K31 is the cell I want my formula to refer to if week one does not have the same job number as week two.

Sorry that this is so confusing! : )

It's indeed hard to describe the organization of one's worksheet. I don't think I could have done better. After seeing your worksheet, I can now say what you need:

In L30 enter:

=K30+IF(COUNTIF($B$9:$B$16,B30),VLOOKUP(B30,$B$9:$K$16,10,0),0)

and copy down this as far as needed.

$B$9:$K$16 is the range which you named 'jobnumber'. I think there isn't much of a reason to use a named range here, but you want to, replace that range by its name.

Aladin

 

[Acody]

Thank you so much!!!!
It finally worked!!!
Yippee, yahoo.
I am soooo happy!