Thread: VBA: Match in Vlookup

Hi excel experts.

I've googled my issue alot, and found some possible solutions, but I cannot get anyone of them to work with my workbook.
(Perhabs due to a lack of competance)

Anyway, In my workbook I have 2 sheets "Datagrundlag" and "Meddelelser".

What I would like to achive is, to pull the Tid1, Tid2 and Tid3 data into the "Datagrundlag" sheet, where a match is found with date and name field like this:
A | B | C | D | E |
01-10-2010 | Person 1 | 1510 | 6768 | 36 |

Workbook is enclosed

Thanks alot in advance.

Regards,
Scifo

Here is one way.

First change the information in Meddelelser column C to be dates rather than text. You can always format the dates to have - rather than / separators.

The use a SUMPRODUCT formula to indentify matches. This assumes no duplication of date and person.

Finally use the resulting row number in INDEX formula.

If you want it in code then there are other approaches.

First off the SUMPRODUCT approach. There is information here but to get around the limitations the Evaluate method is needed.
http://www.xldynamic.com/source/xld.SUMPRODUCT.html#vba

You could just use the formular1c1 property.

activecell.Formular1c1 = "
=SUMPRODUCT((Meddelelser!R5C3:R122C3=Datagrundlag!RC[-2])*(Meddelelser!R5C4:R122C4=Datagrundlag!RC[-1])*ROW(Meddelelser!R5C4:R122C4))"

This will provide the Row number, as per example file, which you can then use to transfer data across. You can even overwrite the cell used for the formula.

Or you could use a FIND approach. Find the person and for each find check the date.

Or you could use autofilter.

Or simply two loops.

I've made a loop that inserts the desired values in the correct cells.

However, the cells shows #NAME? instead of the value, but if I enter a cell, and just press enter (without changing the formula) the cells shows the desired values.
Why is that?

Sub Assemble_data_from_medd()
Dim NumOfRows_data, NumOfRows_medd, NumOfRows_frav As Integer
Dim Counter_row, OffsetNum As Integer

Application.ScreenUpdating = False

' Get number of values from meddelelser, fravær and datagrundlag
NumOfRows_data = Application.CountA(Range("A:A"))
NumOfRows_medd = Application.CountA(Sheets("Meddelelser").Range("C:C")) + 3
NumOfRows_frav = Application.CountA(Sheets("Fravær").Range("C:C"))
OffsetNum = "1"

'
For Counter_row = 2 To NumOfRows_data + 1
    Range("C1").Offset(OffsetNum, 0).FormulaR1C1 = "=SUMPRODUKT((Datagrundlag!R" & Counter_row & "C1=Meddelelser!R5C3:R" & NumOfRows_medd & "C3)*(Datagrundlag!R" & Counter_row & "C2=Meddelelser!R5C4:R" & NumOfRows_medd & "C4)*(Meddelelser!R5C5:R" & NumOfRows_medd & "C5))"
    OffsetNum = OffsetNum + 1
Next Counter_row

Application.ScreenUpdating = True

End Sub