Looking up a Range and selecting Values

[bitswit]

Hi,

I have a list of PINCODE as per encl excel. I need my formula to check on the pincode from another list of 2columns PIN_1 and PIN_2. If the PINCODE falls between PIN_1 and PIN_2, then the values of Country, State and City for that row should be copied into cells B2, C2 and D2.

So for my first row, since pincode=110004 falls within the range 110001-110095, so India, Delhi and New Delhi is copied into my worksheet. Similarly, since 210004 cannot be found, empty cells are returned.

Thanks in advance for your help in this..

[pdhawan]

Hi bitswit

I have entered the formula in the attached sheet. Since your list only had 4 sets of ranges, it was easy to write this formula.

If the list increases it might get a bit tedious to write such a long formula. There could be an easier way but this is what I could think of.

Thanks

[sandeep ghoman]

Hi,

I have a list of PINCODE as per encl excel. I need my formula to check on the pincode from another list of 2columns PIN_1 and PIN_2. If the PINCODE falls between PIN_1 and PIN_2, then the values of Country, State and City for that row should be copied into cells B2, C2 and D2.

So for my first row, since pincode=110004 falls within the range 110001-110095, so India, Delhi and New Delhi is copied into my worksheet. Similarly, since 210004 cannot be found, empty cells are returned.

Thanks in advance for your help in this..

Ya very true. This pin code data must be huge so writing such big formulas wont work. Instead we will have to divide the pin code e.g
Maharshtra , Pune, Camp the ( 411001) if first digit is 4 then its Maharashtra, third and fourth digit is 11 then its Pune and last three digit will identify it as camp. You need to have all data with all Cities in India, if it your pin code falls in the given range then it will pull the value. You can try with this logic or upload the complete data
Please refer http://www.mapsofindia.com/maps/maha.../pincodes.html site to collect data if you do not have with you.

[bitswit]

Thanks for your quick revert, pdhawan !!

However, Im afraid its not so easy since my array will consist of thousands of columns. I have already written a simple vlookup formula to search for PINCODE from column PIN_1 and copy the relevant values of Country, State and City. (file attached)

My entire problem is how to modify this function so that my PINCODE can be found in between the RANGE of 2columns PIN_1 and PIN_2. E.g. if my PINCODE is 151002 then it should be located within the range 151001-151100 (i.e. from row 5) and pick-up the corresponding Country, State and City.

So how to do this pls..