Thread: Lookup jobs belonging to a given client

Dear Forum (here I go again)

Please find attached workbook, which has 2 worksheets, (a) JobList and (b) JobLkUp.

So it would be antisapated that JobList would be a long list rather than just the lenght I have shown for just for the example. A given client would be expected to have many project over the years.

JobLkUp is what I want to try get working as a useful lookup tool. The idea being that the user would select a client from the dropdown in cell C1 of the JobLkUp worksheet, and that the relevant information would automatically populate rows 4 + (however many jobs that particular client has) for columns A:E.

Would seem the right formula in Column A (contract no), would allow for Columns B:E to be handled with regular VLookup, but I'm stuck on getting contract no.s in A4.

Any ideas?

Any insight would be great. Thank you.

Cidona,

Attached is a modified version of your sample workbook. In 'JobLkUp' cell A4 is this array formula. An array formula needs to be entered with Ctrl+Shift+Enter and not just Enter. This formula is then copied down to cell A13 (so it can return up to 10 results. Copy down farther if needed).

=IF(COUNTA(A$3:A3)>COUNTIF(JobList!$B$2:$B$9,$C$1),"",INDEX(JobList!A$1:A$9,SMALL(IF(JobList!$B$2:$B$9=$C$1,ROW(JobList!$B$2:$B$9),9^9),COUNTA(A$3:A3))))

Then, in cell B4 is this formula. This is just a standard VLookup() formula and is not an array formula. It is copied over and down for cells B4:E13

=IF($A4="","",VLOOKUP($A4,JobList!$A$2:$F$9,MATCH(B$3,JobList!$A$1:$F$1,0),FALSE))