W.E.B
asked on
Mulriple Lookup Excel vba
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
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
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.
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.
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:
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
Sample-EE-jeffld.xls
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
Thank you
Added sub and button to reformat the Zones sheet to the ZonesLookup sheet.
Sample-EE-jeffld.xls
Sample-EE-jeffld.xls
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.