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

 

[Krishnakumar]

Hi,

In D3 and copied down,

=LOOKUP(2,1/(($H$3:$H$8=B3)*(C3>=$I$3:$I$8)*(C3<=$J$3:$J$8)),$K$3:$K$8)

HTH