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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
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
 

Forum statistics

Threads
1,136,504
Messages
5,676,254
Members
419,616
Latest member
quickflip

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top