Looking up values in several ranges VBA

I have a table with street names (I've attached a workbook with dummy names) and postal code; the table/listobject is called "Streetnames".

I would like Excel to retrieve the postal code based on street name and number and the data will be displayed in a userform.

What is the most elegant method to retrieve the data?


Massimo ScolaInternshipAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Question: 101 Albert street is what record ?
for me it is row 10 but I see you highlighted 8, 9 and 10 ??

What is expected to put the postal code and get the data filtered ??

You mention to put the data in a userform but the file you posted is .xlsx that contain no userform

I guess you need to be more specific on what you want.

pls try in G5

=SUMPRODUCT(--(Streetnames[Street]=G2),--(Streetnames[From]<=G3),--(Streetnames[To]>=G3),(Streetnames[Postal Code]))

Open in new window

=IF(SUMPRODUCT(--(Streetnames[Street]=G2),--(Streetnames[From]<=G3),--(Streetnames[To]>=G3),(Streetnames[Postal Code])),SUMPRODUCT(--(Streetnames[Street]=G2),--(Streetnames[From]<=G3),--(Streetnames[To]>=G3),(Streetnames[Postal Code])),"Not Found")

Open in new window

Massimo ScolaInternshipAuthor Commented:
Apologies: I highlighted those rows because a street can be in two or three postal codes.
Yes, it is just a xlxs workbook to show you the records.

Attached you will find the new worksheet with a userform, using the first formula by Rgonzo1971. I've also added an addition column to the worksheet.

I've put the formula inside the evaulate function:

Dim strPostalCode As String

strPostalCode = Application.Evaluate("=SUMPRODUCT(--(Streetnames[Street]=""" & Me.cboStreet.Value & """),--(Streetnames[From]<= """ & Me.txtNumber.Value & """),--(Streetnames[To]>=""" & Me.txtNumber.Value & """),(Streetnames[Postal Code]),--(Streetnames[City]=""" & Me.txtCity.Value & """)")

Me.txtPostalCode.Value = strPostalCode

Open in new window

But I get a debug error 13.

Any idea what I'm doing wrong?

then try

strPostalCode = Application.Evaluate("=SUMPRODUCT(--(Streetnames[Street]=""" & Me.cboStreet.Value & """),--(Streetnames[From]<=" & Me.txtNumber.Value & "),--(Streetnames[To]>=" & Me.txtNumber.Value & "),Streetnames[Postal Code])")

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Massimo ScolaInternshipAuthor Commented:
Excellent. Now it works.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.