VLOOKUP in a dynamic setting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here's what I am trying to do. I have the following combination of
information in two different worksheets of a workbook: (Part Number) +
(Machine ID) + (Week - contained in a different cell and not added to the
combination). The (Part Number) + (Machine ID) will be the same from week to
week, but the last piece of information (Week) obviously changes each week.
I wanted to try and do a VLOOKUP on the identifier (combination above) and
return back order and delivery data (listed in specific columns). The
formula is working up until the point where the (Part Number) + (Machine ID)
is contained in multiple weeks and then, of course, the formula does not even
see that there is another line item below the first one it sees.
Unfortunately there may be multiple combinations in the data set which
contain the first two piece of information and only the week varies.

Is there a way to perform a VLOOKUP which will take the (Part Number) +
(Machine ID) value, then look for the week and then return the values
requested? There will never be the same combination of data in a given week,
meaning the (Part Number) + (Machine ID) +(Week) is a unique identifier.
However, because of the way the data is set up, I cannot combine all three
values into one field.

Is what I am trying to do even possible?
Any help / suggestions would be greatly appreciated!!

Thanks,
Liz
 
(Part Number) + (Machine ID) +(Week)

I'll have to make a few assumptions about the ranges containing your date,
so you'll need to change them as necessary:

Lets say A1:A3 contains the data we are looking for:
A1 = Part Number
B1 = Machine ID
C1 = Week

H1:H10 = Part Numbers
I1:I10 = Macine ID's
J1:J10 = Week
K1:K10 = some data you want returned

One way (maybe the easiest) is to put a formula in column G
=H1&I1&J1 copy it down to cell G10.
Then use VLOOKUP using this column:

=VLOOKUP(A1&B1&C1,$G$1:$K$10,5,0)


If the data in column K is numeric, you could use SUMPRODUCT:
=SUMPRODUCT(--($H$1:$H$10=A1),--($I$1:$I$10=B1),--($J$1:$J$10=C1),$K$1:$K$10)

If the data is either numeric or text you could use:
=INDEX($K$1:$K$10,MATCH(A1&B1&C1,$H$1:$H$10&$I$1:$I$10&$J$1:$J$10,0))

which is an array formula, so after you type it in, you will need to hit
Control+Shift+Enter. If done properly, Excel will put braces { } around the
formula

For more info on SUMPRODUCT see:
http://xldynamic.com/source/xld.SUMPRODUCT.html

For some info on array formulas, see:
http://www.cpearson.com/excel/array.htm


Hopefully one of these approaches will do what you need.
 
Hi JMB,
Your first solution, to use the =VLOOKUP(A1&B1&C1) or whatever the columns
were, worked great for what I needed! My C1 is a $C$1 value, but I was not
aware you could combine data that way. I had already tried the concatenate
function on the raw data sheet, but on the summary sheet I was unable to
concatenate them due to the format of the sheet.

Thank you so much!!!!

Liz
 
You are welcome. Thanks for the feedback!

Liz said:
Hi JMB,
Your first solution, to use the =VLOOKUP(A1&B1&C1) or whatever the columns
were, worked great for what I needed! My C1 is a $C$1 value, but I was not
aware you could combine data that way. I had already tried the concatenate
function on the raw data sheet, but on the summary sheet I was unable to
concatenate them due to the format of the sheet.

Thank you so much!!!!

Liz
Click to expand...
 
Back
Top