lookup based on two conditions

I know that there are bunches of threads concerning lookups with multiple criteria, but I just can't figure out how to translate one to my situation. I want to return a value based on an item name which is in column A, and an operation which is in row 1.

The array from which I need to look up the value contains part number in column B, operation in column G, and the actual value I need returned in column H.


So I need to return something like this:

__________Operation 1

PartA-----------5


From this:

...Part A...Operation 1...5

Thank you for the help and please let me know if this is unclear.

Use Index/Match

e.g

=Index($A$1:$D$100,Match(G2,$A$1:$A$100,0),Match(F2,$A$1:$D$1,0))

where A1:D100 contains your entire table and G2 and F2 contain the values to look up in column A and Row 1, respectively.

I don't get your sample... why the x's.. should the times that are in column C of sheet 2 be where the x's are? And is column C of sheet 2 what you are actually trying to return based on column A and B?

Is this sample really representative of your actual?

In your actual sheets are the items in column B of Sheet2 exactly matching Row 1 of Sheet1? They ideally should match for you to get results...

I changed your column B in the attached to show Operation1 instead of Op1, etc.. and the formulas now work.

To answer your question as best as I understand it, no they do not match up. Sheet 2 is a master list of all parts and their operation times. Sheet one is only parts that have orders made against them. So all parts in sheet one can be found somewhere in sheet two, but not all parts in sheet 2 are contained in sheet 1.

See attached...

Formula used in H2:

=INDEX(Sheet1!$A$1:$L$4,MATCH(B2,Sheet1!$A$1:$A$4,0),MATCH(F2,Sheet1!$A$1:$L$1,0))

copied down.

Note: You currently have your workbook set for R1C1 referencing. To go back to the A1 style... go to Tools|Options and uncheck R1C1 reference style from the General tab.

Ok.. I misunderstood then... hold a few moments...

Ok see attached...

Formula in B2 of Sheet1:

=SUMPRODUCT(--(Sheet2!$B$2:$B$24=$A2),--(Sheet2!$F$2:$F$24=B$1),Sheet2!$H$2:$H$24)

copied across the table horizontally and vertically.

If you don't want 0's to show, you can go to Tools|Options and remove the Zero Values checkbox from the View tab.

Try a Pivot Table... see Sheet 4 attached.

While in Sheet2, go to Data|Pivot Table...

Click Next and make sure the entire table is covered in the suggested range..

Click Next, Click Layout

Drag Part Name to the Row area,
Drag Operation to Column area,
Drag Time to the Data area (and make sure it says Sum of Time. If not, double-click and select Sum).

Click Ok.. then click Next.

choose your destination and click Finish.

I think that's way I suggested the formula approach the first time....

.. can you perhaps add a column to Sheet1, that shows Current/Not Current and then include that column in the Row area of the Pivot Table, and then filter for the Pivot Table to show the Current only data?

Well the pivot table automatically creates Column A in Sheet1.. so you would be doing some circular referencing....

So you would have an extra column A... and use the formula in Sheet2..

=If(isnumber(match(B2,Sheet1!A:A,0)),"Active","Inactive") copied down.

Sample attached... sheet1