# lookup based on two conditions

1. ## 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.

2. ## Re: lookup based on two conditions

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.

3. ## Re: lookup based on two conditions

I am still not getting it to return a value, I am getting a N/A error even when the value is in the table. The error is probably on my half, and I don't doubt your formula will work, but I have included an example of what the workbook looks like to try to make it more clear. Sheet one shows the Part names and operations in columns and rows respectively, and what I have left as "x" are the time values that I am trying to lookup and return. Sheet 2 shows the table from which I am trying to look up the time values.

4. ## Re: lookup based on two conditions

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?

5. ## Re: lookup based on two conditions

Sorry, I don't know what I was doing with the x's, but you are correct that is where I want the times from column c of sheet2 to go, I have replaced them in the attachment. This sample is representative of what I want to happen but much simpler than the tables I have. The actual sheets contain much more information (though not important to me). If this sample is not helpful to you I can try to come up with a more representative sample but it may take me a minute or two. I will need to make significant changes to the original before I could post it as a sample, but please let me know if you think it would be helpful. I really appreciate you helping me out with this.

6. ## Re: lookup based on two conditions

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.

7. ## Re: lookup based on two conditions

Attatched is a much more accurate version of the worksheets. Sheet 2 is what I have, sheet 1 is what I am trying to fill in with time values. Thanks again.

8. ## Re: lookup based on two conditions

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.

9. ## Re: lookup based on two conditions

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.

10. ## Re: lookup based on two conditions

ok, I will try to work with what you have, but this is sort of backwards from what I need. I already have the times entered in sheet 2 so I am trying to lookup and fill them in sheet 1. Correct me if I am wrong, but I think in your example they are being filled into sheet 2 from sheet 1.

11. ## Re: lookup based on two conditions

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

12. ## Re: lookup based on two conditions

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.

13. ## Re: lookup based on two conditions

Success! Thank you so much.

14. ## Re: lookup based on two conditions

hmm...althought your formula does work excellently, once copied over the number of cells required, my workbook slows down to intolerable speeds. Is there any possible way to do this in a simpler fashion? If not, I still thank you for all of your time and help.

15. ## Re: lookup based on two conditions

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.

16. ## Re: lookup based on two conditions

the only problem I forsee with this strategy is that sheet 2 is a master list of parts and their operation times, whereas I only want to view those with current orders (those listed in sheet 1). The reason is that I have more to do after this list is generated.

17. ## Re: lookup based on two conditions

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?

18. ## Re: lookup based on two conditions

worth a try...I could create another column in sheet 2 to not if a part number was active. What would this function look like? Something to say if the value in column B of sheet 2 can be found in column A of sheet1, then it is active. I have played around with a few but can't get anything to work.

19. ## Re: lookup based on two conditions

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.

20. ## Re: lookup based on two conditions

Sample attached... sheet1

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1