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

Look Up formula using 3 conditions

E

Ecel Dumbo

Member
Hello,


Please refer the attached worksheet and kindly suggest a look up formula using three conditions.


http://speedy.sh/PrG5n/Look-Up-formual-using-3-conditions.xls


Thanks

Excel Dumbo
 
Hi ,


A similar question has been asked recently here :


http://chandoo.org/forums/topic/how-to-look-up-data-with-3-criteria


Can you go through the topic , and see if it helps ?


Narayan
 
Hello narayan...i tried having a look. i am getting error values here. As i need to do a vertical and horizontal look up, it will be a different case as compared to what you have shown. Please advise.
 
Hi ,


I don't think there is a difference.


We do not need to consider the output table which has the formulae ; we need to consider the input table.


The input table in the earlier question has 4 components :


1. The type of product viz. Trains , Planes and Automobiles ; the input data for these 3 products is organized vertically , in 3 sections.


2. Within each section , you have the various models , which are of course the same for all products viz. Something 1 , Something 2 , Something 3 ,...


4. Within each section , running across the different columns , you have the two years under consideration FY 12 and FY 13.


5. Within each year , you have the 12 months.


The method used is as follows :


a. Search for the individual product name in column A ; this will give us the starting point.


b. Using this starting point , search for the model.


c. Search in row 21 for the text FY 12 or FY 13 , depending on what has been entered by the user.


d. Search in row 22 for the text JAN , FEB , MAR ,... depending on what has been entered by the user.


e. Add the two values obtained in (a) and (b) to get the required row.


f. Add the two values obtained in (c) and (d) to get the required column.


Can you clarify whether your data is organized differently ?


Narayan
 
Hi Narayan,


I used the above principle. Can you please have a look at the attached file ?


http://speedy.sh/rZbau/004-Look-Up-formual-using-3-conditions-v2.xls


Thanks
 
Thanks a lot narayan it worked like MAGIC. Thanks heaps.


Could you please have time to explain the 3 portions in the formula , so that I can understand how it works ?


OFFSET($1:$1

),,1000),

$42:$42


VLOOKUP($A27,OFFSET($1:$1,MATCH($C27,$A:$A,0),,1000),MATCH($B27,$42:$42,0),FALSE)
 
Hi ,


The second parameter for the VLOOKUP function is a range , over which the VLOOKUP will work ; within this range , the first column will be searched for the lookup value , and if found , the third parameter for the VLOOKUP function will be used as the column number within the range e.g. suppose we use =VLOOKUP(5,C11:K55,4,FALSE) , we are doing the following :


1. See if the value 5 ( the first parameter ) exists in the range C11:C55 ( the first column of the second parameter ).


2. If it does not exist , return a #N/A error.


3. Suppose it is found in C37 ; then return the value in F37 ( using the third parameter 4 , F37 is 4 columns from C37 ; 1 will return the value in C37 i.e. the lookup value itself , 2 will return the value from D37 , 3 will return the value from E37 and so on ). Note that the number of columns in the range C11:K55 is 9 , and so the third parameter for the VLOOKUP function cannot be 10 or more. If it is , you will get a #REF! error.


In your case , the third parameter is given by the following formula :


MATCH($B27,$42:$42,0)


This will look to see if the value in B27 ( FEB ) exists in row 42 , and if so , in which column ; if cell A42 has FEB in it , the above MATCH function will return 1 , if cell B42 has FEB in it , the MATCH function will return 2 and so on. In your case , it will return a value of 6.


The second parameter is given by :


OFFSET($1:$1,MATCH($C27,$A:$A,0),,1000)


What this does is it takes the entire first row ( $1:$1 ) as the reference point , and uses the following offsets :


A row offset given by MATCH($C27,$A:$A,0) ; this will look for the contents of C27 ( Sensar ) in column A ; since this is present in A76 , this will return a value of 76.


A column offset of 0 , since we have not used any value


The value of 1000 is just an arbitrary number which will decide the height ( number of rows ) of the range.


Putting all of the above together , the second parameter to the VLOOKUP function will be :


$77:$1076


The VLOOKUP function will now look for TM72003 ( which is the text in A27 ) in column A , starting from A77 through A1076 ; since it finds this in A85 , it will now return the value in F85 ( since F85 is 6 columns away from A85 ).


Narayan
 
Thanks a lot narayan. For taking this amount of time for explaining this to me in a very simple, clear and concise way.


The worksheet I had posted is a sample worksheet. The larger file I am working on will have the data on one sheet and the look up on anoother sheet. I guess OFFSET($1:$1 will not work unless both the look up data and the answer are all in one sheet.


Please kindly check the below work book


http://speedy.sh/CHkEb/004-Look-Up-formual-using-3-conditions-v3.xls


I am greatly humbled by your effort to take time out and explain this to me .


Appreciate it. I hope the creator of this site is looking at this great effort of yours
 
Hi ,


The earlier posted formula has to be changed to take into account the fact that now you are looking up the value "TM72..." not in column A , but in column C.


Use this formula :


=VLOOKUP($E2,OFFSET('Look UP Data'!$A$1,MATCH($F2,'Look UP Data'!$C:$C,0),2,1000,30),MATCH($G2,'Look UP Data'!$5:$5,0)-2,FALSE)


where the changes have been highlighted. Of course , since your lookups are all to be done on data which is in a different sheet , the sheet name has to be included in all the range references.


The 30 is an arbitrary number to include all the data columns ; if your data is going to extend to additional columns beyond column AD , then you will have to increase this number.


Narayan
 
Hi Narayan,


Could you please check the below file and advise what mistake is there in the formula ?


Thanks


http://speedy.sh/SHc4j/Look-UP-with-three-conditions.xls
 
Hi ,


In D2 , please use the following formula :


=VLOOKUP($A2,OFFSET('Quota Adjustments'!$A$1,MATCH($B2,'Quota Adjustments'!$C:$C,0),2,1000,40),MATCH($C2,'Quota Adjustments'!$5:$5,0)+13,FALSE)


Narayan
 
Thanks a lot Narayan. I thought you were not here today. I emailed you regarding this. Please ignore those emails. It worked. If you have some time, could also please explain the +13 concept in the formula... thanks
 
You must log in or register to reply here.
Back
Top