# Difficult Lookup problem

#### FrigidDigit

##### Board Regular
I have a difficult (for me anyway) lookup problem which I hope someone can help me with.

I have a list of data in a worksheet in the following layout:

Company Name;File Name;Contract Number;Contract Modification #;Contract Date

I have another summary sheet that draws information from this list of data in this format

Company Original Contract Mod 01 Mod 02
XYZ 2004/06/01 2004/06/01 2004/06/01

I need a lookup function to lookup the company name in the list of date, lookup the ConMod number based on the column the formula is in and return the contract date.

Is this possible? I have spent most of last night experimenting with index,vlookup match etc but failed to find a solution. I would really appreciate any help.

Thanks

FD

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
A PivotTable might do it.

Have you tried doing a PivotTable? Here's one I did: ( did a Sum on date ... but as only one date per cell of the table will be in place, it's fine. Formatted the table by going into PivotTable field settings, and clicking on Number. )
Book1
ABCDEFGHIJKL
1CompanyFilenameContModDateSum of DateMod
2Funlopmyf13124001-AprCompanyCont012
3Funlopmyf13124103-MayFunlop31241-Apr3-May17-Jul
4Funlopmyf13124217-JulMarx147524-Apr5-Jun7-Aug
5Marxmyf21475024-AprFirebone8241-Jan23-Mar30-Apr
7Marxmyf21475207-Aug
8Firebonemyf3824001-Jan
9Firebonemyf3824123-Mar
10Firebonemyf3824230-Apr
14
Sheet1

Hi,

Try:

=INDEX(\$E\$2:\$E\$7,MATCH(\$G3&H\$2,\$A\$2:\$A\$7&\$D\$2:\$D\$7,0))

Confirmed with Ctrl + shift + enter in H3 then dragged right / down.
Book2
ABCDEFGHIJ
1Company NameFile NameContract NumberContract Modification #Contract DateOriginal ContractModMod
2XYZ1100Original2002-10-10CompanyOriginal12
3AAA2101Original2002-10-11XYZ2002-10-102002-10-122002-10-15
4XYZ310212002-10-12AAA2002-10-112002-10-132002-10-14
5AAA410312002-10-13
6AAA510422002-10-14
7XYZ610522002-10-15
Sheet1

Thanks for the response guys!

Appreciate the help. I won't be using a pivot table because of the strictly specified layout, I will be experimenting with FW's formula.

regards

FD

Replies
1
Views
204
Replies
3
Views
124
Replies
1
Views
139
Replies
4
Views
214
Replies
3
Views
288

1,203,453
Messages
6,055,530
Members
444,794
Latest member
HSAL

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back