MATCH data between two sheets.

[ranaray]

Hello experts,


Please pull me out of this - I am struggling to make anything out of this formula.


Need some urgent help to get a formula working. I think I have partly cracked the formula but my boss has added a spanner to the wheel.


The formula needs to be in ‘Data Sheet’ - column G in the attached workbook

The formula should 1st check if column C in ‘Lookup Sheet’ has the word “ALL”, if yes then formula in ‘Data Sheet’ - column G =IF(COUNTIF(Category,C2),"Non-SLA","")

However if column C in ‘Lookup Sheet’ not equal to “ALL” the formula in ‘Data Sheet’ - column G = ??????? where It should consider bother words in column ‘B’ and ‘C’ in the Lookup sheet tab.


I hope I have explained it well, please let me know if not.


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


Thanks ever so much

Rana

 

[kaushik03]

Hi ranaray,


Thank you for your question and uploading the sample file.


However, I am having hard time in understating your end rrequirement here.


I am not able to relate your formula [IF(COUNTIF(Category,C2),"Non-SLA",)] in pertinent to your explanation.


Let me understand this in my own way and ask you some questions in order to clarify my doubts:


1)I do not understand your following statement:

The formula should 1st check if column C in ‘Lookup Sheet’ has the word “ALL”, if yes then formula in ‘Data Sheet’ - column G =IF(COUNTIF(Category,C2),"Non-SLA","")


If the formula(at G2 of data sheet) finds the word "ALL" at column C of ‘Lookup Sheet’,then what you are expecting the formula would return you?


[=IF('Lookup sheet'!C2="all","what?",if not then what?)]


2) How this approach is related to any one of the column of the "Data sheet"


3)Please also elaborate the below statement with a manual output/example that you are expecting:


if column C in ‘Lookup Sheet’ not equal to “ALL” the formula in ‘Data Sheet’ - column G = ??????? where It should consider bother words in column ‘B’ and ‘C’ in the Lookup sheet tab.


Looking forward to your responses.


Kaushik

 

[ranaray]

Hi Kaushik,


Thank you for coming back. I will try my best to explain, but please keep those questions coming in.


1)=IF('Lookup sheet'!C2="all","what?","if not then what?")]


RR:- I have named two ranges in the Lookup sheet. Catagory and Item.

So if IF('Lookup sheet'!C2="all","then it should look for the word in catagory range only","if not then then it should look for the word in the item range?")]


2) How this approach is related to any one of the column of the "Data sheet"

RR - The search range should be 'Catagory' and 'Item' in the data sheet. Thus if the word appears in catagory or item it should say 'Non-SLA'


3)Please also elaborate the below statement with a manual output/example that you are expecting:


if column C in ‘Lookup Sheet’ not equal to “ALL” the formula in ‘Data Sheet’ - column G = ??????? where It should consider bother words in column ‘B’ and ‘C’ in the Lookup sheet tab.


Its actually related to point 1 & 2 above. I think you asked me the right questions in point 1 & 2


Thanks

Rana

 

[kaushik03]

Hi Rana,


Thank you for your explanation abut confusion still persists!!


In point number one you have said that:

1)You have created two named ranges Catagory and Item in look up sheet, however, I could see only "category" but no "item". But I assume, you refer Col C as "item" named range.


2)You have also said that IF('Lookup sheet'!C2="all","then it should look for the word in catagory range only","if not then then it should look for the word in the item range"


That means the search ranges are "Catagory" and "Item" in Lookup sheet.

please clarify which word the formula should look for...."all" or it's something else?


In point number 2 you have mentioned:


1)The search range should be 'Catagory' and 'Item' in the data sheet...which actually contradicts your point number one explanation.


I have a disconnect here...can u plz clarify these points?


I am really sorry as I am not able to catch your points quickly.


Regards,

Kaushik