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

Multiple Tabs / Look up intersecting values and place result new tab

C

chloec

Member
Hi. Looking for some help with a vlookup/index/match formula. Worksheet has 3 tabs. Data samples at Google Docs: http://bit.ly/zGKtNL. Tried this every which way but can't seem to figure it out. Reaching out to excel ninjas for help. Thanks in advance!


Tab 1: Where I plan to post my results

Tab 2 & Tab 3: Used for lookup. Lookup results posted to tab fields in tab 1.


Formulas for Tab 1 represent:

For each item in Tab 1 - match code & name and look up same value in tab 2. If tab 2 has a date, then write the date in the appropriate cell in Tab 1. Then look up same value in tab 3. If tab 3 has a 1, then write the 1 in the appropriate field.


Rules:

If date and "1" is present in tab 2 / 3, Date will always win (post to tab 1).

If no date is present in tab 2, Number "1" will win in tab 3 (post to tab 1).

If no date, nor "1" is present - leave cell blank (blank in fields in tab 1).

If there's no matching code and name combo - write "Issue" (post "issues" in tab 1)


Solution Example:

Tab 1: Cell B3 = 1/1/2010

Tab 1: Cell B4 = "1"

Tab 1: Cell C3 = 1/3/2010

Tab 1: Cell E3 = "1"

Tab 1: Cell B5 = "" (Blank)

Tab 1: Cells F3:F8 = "Issues"


Can anyone understand what I'm trying to do here? Thank you so much for the assist!
 
This should be doable with some combination of if(vlookup blah blah blah.


Can you upload a better sample doc somewhere? I'd like to see this with the multiple worksheets
 
This can be achieved by index & Match formula.


Just an example below change according to ur tab & range

=INDEX($C$2:$C$5,MATCH(a2&b2,$A$2:$A$5&$B$2:$B$5,0))

Index(result u want to display,MATCH('value to be matched',Range to look up for ,0)) its an array formula


write this formula in both c3 & e3 & in f3 write an IF formula for your conditions.
 
Hi There, Dan - I will try to move the data in the google docs to multiple spreadsheets, It will take an hour or so - when I arrive at work.


Srinidhi - my goal is to have the same formula in all the cells that automagically calculates the rules based on the data in tabs 2 + 3. Do you think this is possible?


Thank you kindly for the assistance!
 
Hi Chloe ,


I am somewhat confused by your "rules" ; let us consider the first instance viz. matching a code of "1111" and a name of "Apple" with the range A12:A18 and B11:F11 ( Tab 2 ) ; this is accomplished by a formula like :


=MATCH(Sheet1!A3&Sheet1!B2,Sheet1!A12:A18&TRANSPOSE(Sheet1!B11:F11),0)


entered as an array formula ( with CTRL SHIFT ENTER ).


But when this combination finds a match , what is supposed to happen ? You say :


If tab 2 has a date, then write the date in the appropriate cell in Tab 1. Then look up same value in tab 3. If tab 3 has a 1, then write the 1 in the appropriate field.


What is the appropriate field ? Suppose B22 did have a 1 , where would this 1 have gone , B3 ?


Narayan
 
Hi There,

Dan_I: the google doc has been edited with the data in tabs 2,3. Reminder: I would like to have formulas in Tab 1 - cells B3 - G8 that looks at tab 2 and tab 3 to see what values are to be displayed in tab 1. I gave some sample answers in red in tab 1.


Narayank991:

The rules I made are a priority of what needs to go in the tab 1 field.


So the formula in cell B3 must look for intersection of CODE 1111 and Name Apple in tab 2. If it finds a date at the intersection of 1111/Apple in tab 2, it can stop and put the date in. The formula is complete // FULL STOP.


However, if tab 2 date field is blank, we need perform a 2nd calculation(but using the value "1" as a tick mark in tab 3). Formula in B3 must look in tab 3 and find the intersection of 1111/Apple (if available) and check to see if there's a "1" in that field. If there is, it puts a "1" in Tab 1,cell B3 (which technically it won't beceause we actually found a date in tab 1 first). If formula can't find a match, it could write "issues" or leave blank if there's neither a 1 or a date.


Does this help?
 
Hi Chloe ,


First let me admit that my earlier formula is wrong. Please ignore it.


Thanks for clarifying ; let me summarise :


1. Look for a match in Tab 2 ; if there is a match , if there is a date in the intersection cell , copy that date in the corresponding cell in Tab 1.


2. If the intersection cell in Tab 2 is blank , or there is no match in Tab 2 , then look for a match in Tab 3 ; if there is a match , if there is a 1 in the intersection cell , copy that 1 in the corresponding cell in Tab 1.


The following cases may arise :


a. There is no match in Tab 2 or Tab 3


b. There is a match in Tab 2 , but instead of a date , there is some other data i.e. the intersection cell in Tab 2 is not blank.


c. There is no match in Tab 2 , and proceeding further , a match in Tab 3 , but again , instead of a 1 in the intersection cell , there is some other data.


In all the above 3 cases , the text "Issues" will be put in the intersection cell in Tab 1.


Hope all of this is correct.


Narayan
 
Hi Narayank991: You got it! Thank you for the efficient summary.


Clarity on cases:

For cases b+c - I never envisioned there to be some other data in those cells. For example - Tab 2 will either have a date or be blank. Tab 3 will either have a 1 or be blank. However, if it's easy to plan for this - let's include it.


Otherwise, it is as you summarized - and maybe I should add item #3: if there is a match in tab 2 or tab 3, but if the matching cells are blank (or empty), leave the cooreponding cell in tab 1 blank.
 
Hi Chloe ,


Can you check out the following link ?


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


Narayan
 
Narayan,

Thank you very much. I can't seem to figure out how you solved this, with all the multiple tabs and formulas. It appears it is well beyond my knowledge of excel formulas. I'm sorry to put you through all this trouble. Unless you have any other knowledge or a simpler way to get this accomplished (or you have the patience to explain it) then I feel like I may have to try something different.

Do you see any other ways of solving this? Perhaps via Pivot? I'm going to spend some time reviewing this to see if I can figure out what you did. I'm also going to port through this page to see if perhaps I can approach it another way (or learn about INDIRECT formulas): http://chandoo.org/wp/2010/11/09/2way-lookup-formulas/

Thanks!
 
Hi Chloe ,


I am sure there must be a better and easier way of doing it ; however , this was done in a hurry , which is why it looks so complicated. If I write down the steps by which it works , you will have no problem in extending it as far as is required.


You have written down the rules for matching each entry in Tab 1 , with its corresponding entry in Tab 2 and Tab 3 ; this matching is being done in the upper table in Tab 2 and Tab 3 ; if a match is found , the corresponding entry is TRUE , else it is FALSE.


The matching itself is being done for two items ; the first matches the code in the column of codes ; the second matches the name in the row of names ; if both match , only then a TRUE results.


Once there is a match , we need to know the position of the match ; these positions are to be found in the lower table in each tab ( Tab 2 and Tab 3 ). These positions are being stored as addresses , so that the INDIRECT function can be used. Wherever there is a match but the entry in the matching cell does not meet the other conditions ( being a date or being 1 ) , the result is a #N/A ; only when the conditions are met , is there an address in the cell.


All this matching would not be necessary if we decided that positional matching is sufficient i.e. suppose the code 1111 being less than 2222 cannot occur in the column of codes later than 2222 , then we can just use the position of 1111 as the first item ; 2222 would be the second item , and so on. But I don't think this is correct. The MATCH function allows full freedom in placement of the codes anywhere in the column. The only requirement is that there should not be multiple matches.


I think once you get to know the power of a few Excel functions e.g. OFFSET , INDEX , MATCH , SUMPRODUCT , INDIRECT , it is exhilarating ; probably you tend to use them even when they are not really necessary !


Narayan
 
Ok, Narayan, I used your suggestions as a starting point and came up with this formula.

=IF(ISBLANK(VLOOKUP($A3,'RS Dates'!$A:$AI,$I$1,FALSE)),I3,VLOOKUP($A3,'RS Dates'!$A:$AI,$I$1,FALSE))


I tried to simplify the ask - my worksheet has 2 tabs - 1 of those tabs contains a helper table. The formula above is pasted in the helper tables and compares the items in tab 1 and tab 2:


(1) Use Vlookup formla to find the appropriate cell in "tab 2".

(2) IF that tab 2 cell is blank, use the value in the coresponding field in "tab 1" (in this formula it is I3. Cell I3 contains the text "Pass". However, that cells has two options, either a pass or be blank. I want "pass" to be pasted in the helper table field, because pass is listed in cell I3.

(3) If it's not blank - don't use the value in I3, but rather tell me what value is in the cell from tab 2.


Can you please tell me why the item (2) is NOT working given the inforamtion I've given you?


The orginal ask was much more complex, but I'm trying to break it down into smaller asks and learn as I go.


Thanks in advance!
 
An alternate partial solution:

For this to work the tables have to line up i.e.

1111 on Tab1 Tab2 and Tab3 needs to be in the same cell. Apple needs to be in the same cell on each tab etc


On Tab1:

=IFERROR(LARGE('Tab 2:Tab 3'!B3,1),"") (copy throughout the table}

and format the table with custom number format: [>1] mm/dd/yy;General


This is using a 3D reference.

If tab 2 has a date, it will pull the date. If tab2 is blank but tab3 has a 1, it will pull the 1. If both are blank, then LARGE will return an error, which kicks the IFERROR into play and returns "".


This won't work with your file given the tabs won't necessarily line up, but something to think about if you can get the data to that point.
 
Hi Chloe ,


I am not very clear on the formula that you have given ; not because the formula is wrong , but because of the way you are referencing the cells ( e.g. $A3 which means the column will not change , but the row can change ; when you copy it across columns , the reference will continue to remain $A3 ; is that what you want ? ).


VLOOKUP($A3,'RS Dates'!$A:$AI,$I$1,FALSE) looks up the value in $A3 , in the column A in the tab 'RS Dates' ; I assume 'RS Dates' is a different tab from the tab where this formula is ; what value does $I$1 contain ? As you copy this formula to different cells , this $I$1 will not change.


So , the above use of VLOOKUP will not return a cell address ; the function will see whether the value in A3 is present anywhere in the column A , and return the corresponding value from the column specified by the value in I1 ; so if the value in A3 is 37 , and 37 is present in the cell A17 , and the value in I1 is 7 , then the result of the VLOOKUP function will be whatever is the value in cell G17 ( because G is the 7th column ).


Thus , if there are multiple matches in the column A , only the first match will be used ; the others will never be used.


Second , the ISBLANK function will be TRUE only if a cell is genuinely blank , without any formula or data in it ; if you have a formula such as :


=IFERROR(....)


where the formula within the brackets can be anything , then , in the case of an error , the IFERROR will display a blank , but the ISBLANK function will return FALSE ; instead of using the ISBLANK function , use the check =VLOOKUP(...)="" , and see.


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