VLOOKUP Not Updating when Data Repeated

[Ryanchris]

Hello!
I have a quote tool that uses VLOOKUP formulas. I use userforms to gather data and upload it to an excel sheet. I have noticed that the formula works the first time I input a product. However, if I enter the same product again, the formula seems to only recognize the first instance even though the second instance shows up within the VLOOKUP range. Is there an update function or something that is needed to find these second and third instances of the same product?

Thank you so much for your help!!

[Paul_Hossler]

I think that the way VLookup works -- only finds the first

You can try .Find or .Filter if you want them all


Something like this



Option Explicit


Sub drv()
Dim r As Range


Set r = FilteredData(ActiveSheet.Cells(1, 1).CurrentRegion, 1, "AAA")
If Not r Is Nothing Then r.Interior.Color = vbRed


Set r = FilteredData(ActiveSheet.Cells(1, 1).CurrentRegion, 1, "ddd")
If Not r Is Nothing Then r.Interior.Color = vbGreen


End Sub


Function FilteredData(r As Range, c As Long, v As Variant) As Range


On Error GoTo NiceExit


With r
If .Parent.FilterMode Then .AutoFilter

.AutoFilter
.AutoFilter Field:=c, Criteria1:=v

'hide header row
.Rows(1).EntireRow.Hidden = True
Set FilteredData = r.SpecialCells(xlCellTypeVisible)
.Rows(1).EntireRow.Hidden = False

.AutoFilter
End With


Exit Function


NiceExit:
Set FilteredData = Nothing


End Function

[macropod]

For a formula-based solution, see: http://www.techsupportforum.com/forums/f57/excel-formula-issues-375768.html#post2567119

[Ryanchris]

Thank you for both responses. I was actually able to take something from both and solve a different issues I was having! However, in this case, I need to clarify what I am doing. See example below:

Sheet 1
A B
TLC-03 10
TLC-03 15
TLC-03 20

Sheet 2, Cell A2 needs to calculate the number of TLC-03 parts are ordered. In this case the total is 45. With VLOOKUP, my current cell A2 only calculates 10.

I hope that helps clarify and so sorry that my first question wasn't so clear.

Thank you in advance!!

[Ryanchris]

NOticed the data didn't transfer over well...
Column A is the part, column B is the qty.

[大灰狼1976]

I can only guess without the attachment.
I think it's better for you to use SUMIF here.

--Okami

[Ryanchris]

I will try a few sumif functions and see if I can get that to work. Thank you!

[macropod]

For example:
=SUMIF(Sheet1!A:A,"TLC-03",Sheet1!B:B)

[Ryanchris]

SUMIF was the way to go!

Thanks all!!