Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Mulriple Lookup Excel vba

Posted on 2015-02-08
8
181 Views
Last Modified: 2016-02-10
Hello,
Can you please help.
I have hundreds of postal codes (Sheet1),  
I need to look up the zones (Sheet2), then look up the prices (Sheet3).

I wonder if there is a macro that can do this.

Sample attached.

Any help is appreciated.
Thank you
Sample.xls
0
Comment
Question by:W.E.B
8 Comments
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 40597300
Is there any possibility of having the Postal Codes listed separately in the Zones sheet? For example, instead of listing B0A-B0C on one row, use 3 rows...

B0A    Y
B0B    Y
B0C    Y

And also only use 2 columns, with all PCs in column A and the zones in column B.

If that is possible, you could then use a simple VLOOKUP to return the zone....

    =VLOOKUP(A3, Zone!A:B, 2, FALSE)

Then to find the Envelope and Pack, you could use HLOOKUP....

    Envelope  =HLOOKUP(B3, Price!$4:$7, 3, FALSE)
    Pack  =HLOOKUP(B3, Price!$4:$7, 4, FALSE)

And given that the price for the different weights increases by $2.00 for each lb, you could use these formulas for the different weights...

    5lbs  =G3+2*5
    10lbs  =G3+2*10
    25lbs  =G3+2*25
    50lbs  =G3+2*50

...where cell G3 is the Pack price.
0
 
LVL 18

Expert Comment

by:Simon
ID: 40597314
There could be a macro that would do this, but it would be far easier if you got a fully enumerated list of postcodes per zone rather than ranges of postcodes (e.g. A0A-A9Z). Maybe the macro you are looking for is one to convert that 'zones' listing to a two column listing of all individual postcodes within each range?

Without macros, I think I've done what you needed, simply by converting the zones to a single two column table and using a vlookup against it with the 'range_lookup' argument set to true. That gets the zone (provided that the zones listing is sorted alphabetically) and I then get the price by using the MATCH and INDIRECT functions to get the appropriate value from the prices table.

Please see attached.Postcode-Price-Lookup.xls

I wouldn't bet my job on all the answers being correct without checking the results carefully. The range lookup version of VLOOKUP provides the nearest match without overshooting in the sorted data, so if you're looking for 'BA' and the values in the sorted lookup range go from
'AA'
to
'CA'
it will return the lookup value from the 'AA' row, which may not be appropriate in your situation. Far, far better to get a fully enumerated list of postcodes per zone and do the vlookup on exact matches only.
0
 
LVL 12

Expert Comment

by:Jeff Darling
ID: 40597398
It looks like you have quite a few responses already, but since I've worked on it, I'll share what I've done.

I assumed that the Postal codes can be enumerated as Base 36 numbers.  this way, I was able to determine if a Postal Code falls within the range.

Excel Sample Attached

Code:
Public Function getZone(txtPostalCodeSearch As String)

Dim ArrPC
Dim txtPostalCodeLookup
Dim iRow
Dim txtZone
txtZone = "Unknown"

Dim rng As Range, cell As Range

Set rng = Range("ZoneLookup!A2:A223")

iRow = 1
For Each cell In rng
 iRow = iRow + 1
 txtPostalCodeLookup = cell.Value

' Get the postal code or codes into an array
ArrPC = Split(txtPostalCodeLookup, "-")

Debug.Print UBound(ArrPC)

If UBound(ArrPC) = 0 Then
 If txtPostalCodeSearch = ArrPC(0) Then
   txtZone = Range("ZoneLookup!B" & iRow).Value
 End If
 Else
If SearchPostalRange(txtPostalCodeSearch, ArrPC(0), ArrPC(1)) Then
    txtZone = Range("ZoneLookup!B" & iRow).Value
 End If
  
End If

Next cell

getZone = txtZone

End Function



Public Function SearchPostalRange(txtSearch, txtStartRange, txtEndRange)

Dim dblSearch
Dim dblStartRange
Dim dblEndRange

dblSearch = BaseToDec(txtSearch, 36)
dblStartRange = BaseToDec(txtStartRange, 36)
dblEndRange = BaseToDec(txtEndRange, 36)

If dblSearch >= dblStartRange And dblSearch <= dblEndRange Then
  SearchPostalRange = True
Else
  SearchPostalRange = False
End If


End Function



Public Function BaseToDec(ByVal strBaseNumber As String, ByVal intBase As Integer) As Double

Dim i As Integer
Dim strDigits As String
Dim strDigitValue As Long

strDigits = Left("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ", intBase)

For i = 1 To Len(strBaseNumber)
strDigitValue = InStr(1, strDigits, Mid$(strBaseNumber, i, 1), vbTextCompare) - 1
If strDigitValue < 0 Then Err.Raise 5
BaseToDec = BaseToDec * intBase + strDigitValue
Next

End Function

Open in new window

Sample-EE-jeffld.xls
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:W.E.B
ID: 40597420
Thank you all.

Jeff,
Excellent, this seems to be working.

2 questions,
1- is there a way to run it as a sub, I want to create a button to run it when I edit the codes.
2- how did you get the postal codes to go into 2 columns? did you run a macro?

thank you very much.
0
 
LVL 12

Accepted Solution

by:
Jeff Darling earned 500 total points
ID: 40597429
I manually edited the Postal codes sheet so that the codes were only in one column.  The ranges of codes are still there exactly as they were before, just on one column.

Do the codes always come in multiple columns?  Is it always the same number of columns? I could write a sub to convert those  columns to one, but it seemed like a trivial thing to do as a one time manual edit.

After double checking my work, I found that Cell D8 needed correcting.   revision attached
Sample-EE-jeffld.xls
0
 

Author Comment

by:W.E.B
ID: 40597450
Hi Jeff,

Ok, I was just curious abut the columns.

what about a button to run it as a sub, can you please help,

thank you much
0
 

Author Closing Comment

by:W.E.B
ID: 40598200
Thank you
0
 
LVL 12

Expert Comment

by:Jeff Darling
ID: 40601640
Added sub and button to reformat the Zones sheet to the ZonesLookup sheet.
Sample-EE-jeffld.xls
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question