Tricky Conditional VLookup

[Kapital]

Hello everyone,

I have been pulling hairs for the past few hours trying to think of the best way to do this. Firstly see below for a sample of my data.

Spreadsheet 1

Column A Column B Column C
01/01/2000 NY Giants 0.96
02/01/2000 Dallas 0.98
02/01/2000 Redskins 0.78


Spreadsheet 2
Column A Column B Column C
01/01/2000 At New York Giants 9
02/01/2000 At Dallas Cowboys 8
02/01/2000 At Washington Redskins 7

So what I want to do is do a vlookup of column A and column B of spreadsheet 1 on spreadsheet 2 so that it returns me column C (from spreadsheet 2). Note the date format is equal in both spreadsheets, however column B only contains part of the text. What is the best way to retrieve column C from spreadsheet 2. In reality, my spreadsheets are very large and contain 5 years worth of historical data and multiple columns.

Much appreciated for any guidance.

[Pepe Le Mokko]

Hi
it would be easier if you posted a sample sheet ( no pics please) - Having to re-type or copy paste data is not my forte.

[Kapital]

See attached. The spreadsheet has two sheets.Dummy.xlsx

[martindwilson]

=SUMPRODUCT(--(Sheet2!A1:A30=A1),--(ISNUMBER(SEARCH(B1,Sheet2!B1:B30))),Sheet2!C1:C30) would work but you need to have some consistency in names ny giants is not in the string new york giants
hmm now i see your real workbook it becomes somewhat harder

[Kapital]

Thanks for the prompt reply. However that's the problem, there is no consistency in the names. But one spreadsheet will always CONTAIN the team name as per the other spreadsheet and vice versa e.g NY giants vs New York giants or Dallas vs Dallas cowboys or AT Washington Redskins vs Redskins etc.

[martindwilson]

the date formats are entirely different how do you know the year on sheet 2?

[Kapital]

Dummy.xlsx

the date formats are entirely different how do you know the year on sheet 2?

That's a different jmatter I have to worry about all together. Anyways the spreadsheets have been fixed. Thanks, still looking for a solution.

[Kapital]

Any ideas Excel guru's out there?

[kvsrinivasamurthy]

Pl see the attached file.

[Kapital]

Pl see the attached file.

And that is why you are the guru and I am the novice. Thankyou so much sir, I will try to implement this on a greater scale and see how I go. Thumbs Up!

[kvsrinivasamurthy]

Welcome. Pl mark the thread SOLVED.