• 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.

Vlookup with multiple criteria

H

hayden_pinto

New Member
I have tried to Vlookup with multiple criteria's with the below mentioned formula

Eg;-

A1 - I have a country name

B1 - I have a state name

C1 - I have to get the capital of the state automatically with the help of a formula


The county state and capitals are in a table

D E F

Country State Capital

India Goa Panjim

India Maharashtra Bombay


i use this formula

{=Index(F:F,(Match(A1 & B1, D:D & E:E,0)))}

Then i press CRTL SHIFT ENTER This makes the calculation very slow and i have 9000 records to tally

Is there another way to get the same result without the delay?

And what does CRTL SHIFT ENTER do ?
 
Hi Hayden ,


The following formula looks monstrous , but it seems to perform the job faster ! Can you check and let us know ?


=INDEX($F$1:$F$10000,MATCH(A1,$D$1:$D$10000,0)+MATCH(B1,OFFSET(INDEX($E$1:$E$10000,MATCH(A1,$D$1:$D$10000,0)),0,0,COUNTIF($D$1:$D$10000,A1)),0)-1)


I have used row 1 as the starting row ; in case it is 2 , please change all the addresses accordingly.


This formula assumes that your table is sorted on Column D , then Column E and then Column F.


Narayan


P.S. For anyone who is interested , I came across this website today :


http://sites.mcpher.com/share/Home/excelquirks/optimizationlink/execution-time-logging
 
Hi


I tryed understanding the formula and got lost at the offset and match part , is there a way i can add 3 criteria's for search.
 
Hi Hayden ,


It can certainly be done , but the formula will become quite unwieldy. Are you averse to using helper columns / cells ?


What is being done in the formula is :


MATCH the country first ; find the first entry for the country ; once that entry is found , COUNT the number of entries for that country ; within these entries try to MATCH the state ; once the state is found , retrieve the corresponding entry from the column for the cities.


What are the three criteria that you want to use ?


Narayan
 
Coloums are as follows

Date - City - Task - Completed


The formula needs to first find the date then the city then task completed and then retrieve the number in the completed coloum.


I currently use this formula but it takes time to calculate as there are over 5000 entries

{=Index(G:G,(Match(A1&B1&C1,D:D&E:E&F:F,0)))}

Then i press CRTL SHIFT ENTER
 
Just a long shot, but since presumably the state names are unique, is it possible to skip the extra criteria about what county it is?
 
Hayden - I have to say I am not sure what you are trying to do exactly, as you seem to be selecting or already have the country and state information and only need to look up the capital of the state - assuming that you don't have two capitals of the same name, in different countries or states then this is just a normal look up.


However if you have two capitals with the same name in either two countries or states, then you will need to do something different.


If you can provide some additional information with an example of the starting data you have, then perhaps a solution can be fount.


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