• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup query

T

tarun_1

New Member
Hi,


I have got a query in excel lookup.


(i wish here an option should be there to upload file for better clarity)


i need to lookup data with database and i need buckets for it.

eg:

[pre]
Code: 
Database:
India	1	3	12	30	50	100	500	1000
Jordan	1	3	12	30	50	100	500	1000
UAE	1	3	12	30	50	100	500	1000

now:
India	1
Jordan	2
UAE	3
should give: 1 to 3 for each country because it falls between 1 and 3

Similarly if we enter 10 it should give 3 to 12. Data is huge and weekly.


My friend has given a complicated IF and Vlookup formula but i think there should be a better way. Also - bucketing should be dynamic w.r.t. database i.e. if we change Jordan - their bucketing should automatically change - Eg.

Revised Database:
India	1	3	12	30	50	100	500	1000
Jordan	0	4	12	30	50	100	500	1000
UAE	1	3	12	30	50	100	500	1000

now:
India	1
Jordan	2
UAE	3
[/pre]
should give: India 1 to 3; Jordan 0 to 4; UAE 1 to 3 in each corresponding cells of respective country.
 
Tarun


Firstly, Welcome to the Chandoo.org Forums


Can I suggest you please read the 3 sticky green posts on the main Forums page.

Had you done that you would have seen: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi,


regret inconvenience.


here is the link of the file

https://docs.google.com/spreadsheet/ccc?key=0AkO2OWXsrX1idDdMR3JMSHdDSmVfX2lqeGU1X2F1NHc


<Regards>

Tarun
 
Hi Tarun ,


Can you try the following ?

[pre]
Code: 
=INDEX($L$14:$L$21,MATCH(VLOOKUP(B2,VALUE(LEFT($L$14:$L$21,FIND("to",$L$14:$L$21)-2)),1)&"*",$L$14:$L$21,0))
[/pre]
Of course , I have modified the >1000 to 1001 to 999999 ; also , I have not included the test for the countries , because I am not very clear on why this is required. For instance , Jordan has a value of 0 appearing in the bucket 1 to 3 ; similarly 4 for Jordan appears in the bucket 1 to 3.


Similar cases appear for other countries also ; India , Pakistan and UAE ( and possibly other countries too ) have values of 0 appearing in the bucket 1 to 3 ; why not modify the bucket to 0 to 3 then ?


Narayan
 
Hi Narayan,


woww...formula is bouncer to me - but - this is indeed working


- but i what if countries database like Jordan changes?


<Regards>

Tarun
 
Hi Tarun ,


I have not yet understood what the countries' database does ; can you please explain it ?


For example , I have seen that a value of 0 for India results in a bucket of 1 to 3 ; how does this work ? After all , even in the countries database , the minimum value against India is 1.


BTW , I forgot to mention that the formula given in my earlier post is an array formula , to be entered using CTRL SHIFT ENTER.


Narayan
 
Hi Narayan,


Formula created by my friend is not accurate.


Countries Database is the Bucketing part- which can vary from country to country. Ideally i was looking out that formula should refer to (Countries Database from) L1:T7 so that if bucketing changes for one country - its resulting Bucket also changes accordingly.


<Regards>

Tarun
 
Hi Tarun ,


If I understand you correctly , this means that every country's buckets can be different ; the formula should refer to the country's bucket area to find out in which bucket the value should be put.


Coming back again to my earlier question : India has a minimum value in the first bucket of 1 ; suppose we have a value of 0 against India ; what should the bucket entry be ?


Narayan
 
Hi Narayan,


Regarding Bucketing -you understood correctly.


Coming back to India - if in case data is not fitting in any bucket then it should give some error or comment like "0 case".


<Regards>

Tarun
 
You must log in or register to reply here.
Back
Top