VLOOKUP inconsistancy

[RICK150]

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
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		CODE	POSTCODE	ZONE			COUNTRY	CODE	START RANGE	END RANGE	ZONE
3		AT	9501				Austria	AT	9500	9599	A
4		AT	9650				Austria	AT	9600	9899	B
5		AT	9894				Austria	AT	9900	9999	C
6		AT	9999				Belgium	BE	1000	2399	D
7		AT	9600				Belgium	BE	2400	2499	A
8		BE	3000				Belgium	BE	2500	3199	C
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

 

[patrickmuldoon99]

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]

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.

 

[patrickmuldoon99]

Can you post this VLookup that you've created?

 

[Airfix9]

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]

Hi RICK150

Please try in D3:

=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]

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.

 

[Airfix9]

This appears to have been asked twice. There is a solution on the other example, would this solve the problem?

http://www.mrexcel.com/board2/viewtopic.php?t=236387

 

[RICK150]

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]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		CODE	POSTCODE	ZONE			COUNTRY	CODE	START RANGE	END RANGE	ZONE	CODESTART
3		AT	9501	A			Austria	AT	9500	9599	A	AT9500
4		AT	9650	B			Austria	AT	9600	9899	B	AT9600
5		AT	9894	B			Austria	AT	9900	9999	C	AT9900
6		AT	9999	C			Belgium	BE	1000	2399	D	BE1000
7		AT	9600	B			Belgium	BE	2400	2499	A	BE2400
8		BE	3000	C			Belgium	BE	2500	3199	C	BE2500
9		BE	1001	D
Codes