Vlookup Help

B

brumanchu

Hello,
I have a data sheet with information in rows and I use vlookup to match the
date/operation/crew and return a value in one of the columns. Now, I have
multiple entries where the same date/operation/crew could occur in the same
day. Ideally, I want to combine math data in the other cells and have one
entry return from my vlookup formula. I already use a helper cell to get the
date/operation/crew criteria concatenated, then use that as my lookup value.

If anyone could provide assistance, I would very much appreciate it.

Thanks,
Bruce
 
B

brumanchu

Example:
A B C D E F
G
1 11/1 C X 100 100 =d/e
=concatenate(A,B,C)
2 11/1 C Y 90 100 =d/e
=concatenate(A,B,C)
3 11/2 D X 85 120 =d/e
=concatenate(A,B,C)
4 11/2 D Y 105 120 =d/e
=concatenate(A,B,C)
5 11/3 C X 97 100 =d/e
=concatenate(A,B,C)
6 11/3 C Y 68 100 =d/e
=concatenate(A,B,C)


I use vlookup(concatenate(A,B,C),A1:G6,6) to return the value in column F
(column G is actually in the column A position, i didn't want to retype it
after I noticed)

The process works great when this is the case. However, not I have data
that looks like this:

A B C D E F
G
1 11/1 C X 40 50 =d/e
=concatenate(A,B,C)
2 11/1 D X 50 50 =d/e
=concatenate(A,B,C)
3 11/1 C Y 90 100 =d/e
=concatenate(A,B,C)
4 11/2 D X 85 120 =d/e
=concatenate(A,B,C)
5 11/2 D Y 105 120 =d/e
=concatenate(A,B,C)
6 11/3 C X 97 100 =d/e
=concatenate(A,B,C)
7 11/3 C Y 68 100 =d/e
=concatenate(A,B,C)

Where on 11/1 crew X worked on both C & D products and output 40 & 50
respectively. Vlookup only returns the first 11/1CX column F value, and I
want to add row 1 & row 2 together to get one value for the date 11/1CX
column F

Hope this clarifies.
Bruce
 
T

T. Valko

OK, you want to sum column F using the criteria concatenate(A,B,C) ?

Try this...

=SUMIF(G1:G10,J1,F1:F10)

Where J1 = concatenate(A,B,C)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top