VLOOKUP inconsistancy

RICK150

New Member
Joined
Nov 9, 2005
Messages
44
Hello there, Once again i've been pulling my hair out for a few days over this issue and now am resorting to you guys and throwing it to the floor since you've helped me so much in the past.

I'm trying to complete a spreadsheet that will auto price shipments going out to foreign countries.

I have the following table :-
Book4
ABCDEFGHIJKL
1
2CODEPOSTCODEZONECOUNTRYCODESTART RANGEEND RANGEZONE
3AT9501AustriaAT95009599A
4AT9650AustriaAT96009899B
5AT9894AustriaAT99009999C
6AT9999BelgiumBE10002399D
7AT9600BelgiumBE24002499A
8BE3000BelgiumBE25003199C
9
Sheet1



Columns B & C are entered by the user. G2 to K8 is part of my lookup table listing all the possible postcodes and areas.

What i'm trying to do is fill Auto fill Column D. I've tried to do this withg a VLOOKUP but if it can't find the codes then it returns the closest value it can find and displays that, sometimes losing the correct matched code and looking elsewhere for the . I want it first to MATCH column 3 to column H, THEN match column C to columns I and J, but find an EXACT match.

I'm willing to add new columns or anythign necessary i just need it to br precise as the incorrect code could mean overcharging or even worse undercharging a customer.

If you need any more information then please let me know and i'll try to help.

Thank you very much in advance for any help anyone could offer to me, as i'm about to pull all my hair out and give my computer a free flying lesson before taking it out to the back garden and giving it a free funeral too, something i shouldn't be looking forward to as much as i am.

THANK YOU

Ricky
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
I've tried to do this withg a VLOOKUP but if it can't find the codes then it returns the closest value it can find and displays that, sometimes losing the correct matched code and looking elsewhere

Vlookup with TRUE specified at teh end will find closest match, if you specify FALSE it will return #N/A if an exact match is not found....
 

RICK150

New Member
Joined
Nov 9, 2005
Messages
44
yes thats exactly what i thought however the formula i've used uses 2 vlookup and it seems though if it finds a country code match then it tries to find the match with the actual postcode numbers. The issue seems to be when it does this it loses the pre found country code and searches for an exact match out of any of the postcodes.

So with the BE in Cell B8, it finds the match then when it gets found it looks at column C8 and tries to find a match, Now other countries may include a postcode range which 3000 falls in, if there is then it returns that zone code, its not being limited to pre matched 'BE' codes.

Does that make sense ?

Any idea?

Thank you and sorry for the lack of description talents.
 

Airfix9

Well-known Member
Joined
Sep 23, 2005
Messages
886

ADVERTISEMENT

How about creating unique reference numbers and using them as a basis for the vlookup. What I have in mind is concatenating cols B & C.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
Hi RICK150

Please try in D3:

Code:
=INDEX($K:$K,MAX(IF($H$3:$H$8=B3,IF($I$3:$I$8<=C3,IF($J$3:$J$8>=C3,ROW($H$3:$H$8))))))
This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.
Copy down.

Remark: If you have thousands of rows maybe you are better of with a solution with auxilliary columns.

Hope this helps
PGC
 

RICK150

New Member
Joined
Nov 9, 2005
Messages
44

ADVERTISEMENT

The formula at the minute has been written by a colleague, and I'm not 100% convinced its anywhere near right.

=IF(B3&C3>=VLOOKUP(B3&C3,G3:K8,1,FALSE),IF(B3&C3<=VLOOKUP(B3&C3,G3:K8,2,FALSE),VLOOKUP(B3&C3,G3:K8,"nd"))


Ideally i'd like to have another column in 'E'. Then in column D I'd want a range finder, which would match B3 to the H columns and return the area H3:J5. If i could get it doing that then column E could use that range to find a match for the postcode in coulmn C but ONLY searching in the area of those postcodes that match 'AT' sat he country code.
 

RICK150

New Member
Joined
Nov 9, 2005
Messages
44
I'll give this resolve a try as soon as i get another chance. So didn't mean to post twice so sorry about that. I'll let you all know and thanks for your help.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,871
Office Version
  1. 365
Platform
  1. Windows
I think the solution in the other thread does work, but here is another one (along the lines suggested by Airfix9) that Ricky might like to consider:

1. In L3 (copied down): =H3&I3
2. In D3 (copied down): =INDEX(K$3:K$8,MATCH(B3&C3,L$3:L$8,1))
Mr Excel.xls
ABCDEFGHIJKLM
1
2CODEPOSTCODEZONECOUNTRYCODESTART RANGEEND RANGEZONECODESTART
3AT9501AAustriaAT95009599AAT9500
4AT9650BAustriaAT96009899BAT9600
5AT9894BAustriaAT99009999CAT9900
6AT9999CBelgiumBE10002399DBE1000
7AT9600BBelgiumBE24002499ABE2400
8BE3000CBelgiumBE25003199CBE2500
9BE1001D
Codes
 

Watch MrExcel Video

Forum statistics

Threads
1,114,093
Messages
5,545,917
Members
410,711
Latest member
Josh324
Top