Vlookup with duplicate values.

[Addictions]

Hi,
Can somnoe help or navigate me on how to solve this problem
I would want to originaly have a vlookup to return values from Table 2 in to Table 1, A, B, C columns. The problem is with duplicates vlookup will always return the first found value.
Any ideas to solve this?

Table 1

Michael

(90)

(80)

(60)

Table 2

Michael	90
Michael	80
Ana	70
Michael	60

Table 1
A B C D
1 Michael (90) (80) (60)


Table 2
A B
1 Michael 90
2 Michael 80
3 Ana 70
4 Michael 60

 

[Fluff]

Don't use whole column references, it's just a drain on resources & could freeze your sheet.

 

[Addictions]

Don't use whole column references, it's just a drain on resources & could freeze your sheet.

I see, would you possibly know how to do it in VBA to look to the last row instead?.

 

[Addictions]

I have below code, but you rightly mentioned not to use whole column range. What would I need to do to change in formula to look at the last row in specific column instead of referecncing whole column. Column A always will have data.

Dim Fnd As Range

Set Fnd = Sheets("Sheet1).Range("1:1").Find("Name")
   If Fnd Is Nothing Then Exit Sub
   Fnd.Offset(, 1).EntireColumn.Insert
   Fnd.Offset(, 1).Value = "Data1"
   With Range(Fnd.Offset(1, 1), Cells(Rows.Count, Fnd.Column).End(xlUp).Offset(, 1))
      .NumberFormat = "dd/mm/yyyy"
      .Formula = "=IFERROR(INDEX(Sheet2!$B:$B,AGGREGATE(15,6,ROW(Sheet2!$B:$B)-ROW(Sheet2!$B$2)+2/(Sheet2!$A:$A=Sheet1!$A2),COLUMNS('Sheet1!$N2:N2))),"""")"

   End With

 

[Addictions]

I am guessing there is no way to implement variable to check the last row as it is inserting formula in to cell.

 

[Fluff]

Please don't be so impatient.

Add this to your code

   Dim UsdRws As Long
   UsdRws = Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row

and the formula would then be

   .Formula = "=IFERROR(INDEX(Sheet2!$B2:$B" & UsdRws & ",AGGREGATE(15,6,(ROW(Sheet2!$B2:$B" & UsdRws & ")-ROW(Sheet2!$B$2)+1)/(Sheet2!$A2:$A" & UsdRws & "=Sheet1!$A2),COLUMNS(Sheet1!$N2:N2))),"""")"

 

[Addictions]

You made my day!.

Thank you so much for your help. It is really much appreciated.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Addictions]

You're welcome & thanks for the feedback.

Just a last question.
Would you be able to reccomend some good content to learn VBA and to practice?.

 

[Fluff]

I've never read any books or watched videos on VBA, so cannot recommend anything.
I've learnt most of what I know from posting here.

 

[Addictions]

Thanks, practice is best!