Finding if a Column contains a value based upon criteria (1 Viewer)

[cyd44]

I am having a problem trying to find and use the correct formula to do the following.

I have a sheet where some of the data can be repeated in multiple rows.
I want to search all rows to find if a row contains the value of two seperate columns:-

Master data is
Fld 1 Fld 2 Fld 3 Fld 4 Fld 5 Fld 6

Secondary Data

Fld1 Fld 2 Fld3 Fld4


What I want to do is to search and check whether each of entries in Field 1 above contains at least one instance of Atonic in Fld 6 of the Master data. I want to put Yes or Know in Fld 4 above.

I understand that this would possibly involve Lookup, Index, If and/or Match functions but I cannot work out how to do this.

The logic is to select Fld1 in each row in the Secondary Data and see if the Matching Fid 1 in the Master Data contains the Value Atonic in Fld 6. If True, I want to place Yes in Fld 4 of the Secondary Data

I hope this makes sense?

Could anybody help

 

[Brianwarnock]

I can't grasp the data layout.

Is all of this on one sheet?

If yes does the secondary data immediately follow the master or is it all mixed up?

Perhaps a spreadsheet of sample data would clarify matters! Xls format please.

Brian

 

[cyd44]

I can't grasp the data layout.

Is all of this on one sheet?

If yes does the secondary data immediately follow the master or is it all mixed up?

Perhaps a spreadsheet of sample data would clarify matters! Xls format please.

Brian

Hi Brian

Sorry for the confusion. The data is on 1 sheet and I am simply trying to find whether a chosen piece of data is contained within the main listing each unique key.

I have a cut/past sheet containing some data which probaly better explains. On this sheet the main data is in the range A1:G20.

I have extracted the unique keys from this range and placed them in M1:O4. In cell R2 there is data that I want to match for each of the Unique keys.

 

[cyd44]

Hi Brian

Sorry for the confusion. The data is on 1 sheet and I am simply trying to find whether a chosen piece of data is contained within the main listing each unique key.

I have a cut/past sheet containing some data which probaly better explains. On this sheet the main data is in the range A1:G20.

I have extracted the unique keys from this range and placed them in M1:O4. In cell R2 there is data that I want to match for each of the Unique keys.

I have beeing trying both Lookup and VLookup for this and have it partially working. Strange though that it will find 1 key but not another and will not not validate the Chooser field for all possibilities. Have attached sheet to show function entered in col L

 

[Brianwarnock]

Your problem is that you need a 2 column lookup, the reason that your approach works sometimes and not others is that it returns the value corresponding to the last match.

I have coded a 2 column lookup for you.

I have changed the value in row 18 inorder to test a no match situation.

The formula is an array formula and is entered by pressing Ctrl+shift+enter

Brian

 

[cyd44]

Your problem is that you need a 2 column lookup, the reason that your approach works sometimes and not others is that it returns the value corresponding to the last match.

I have coded a 2 column lookup for you.

I have changed the value in row 18 inorder to test a no match situation.

The formula is an array formula and is entered by pressing Ctrl+shift+enter

Brian

Hi Brian

Your a genius mate? This has worked fine. Just a question, why do we use a negative in that when True we have N and False Y? It works well but the logic seems strange?

 

[Brianwarnock]

If the match function does not find a match N/ A is returned , this is tested for with the ISNA function , so we have in effect a double negative effect in that if there is a match and thus ISNA is not true we need to use the False result for there is a match.

That sounds as Clear as mud I hope you understand it.

Brian

 

[cyd44]

If the match function does not find a match N/ A is returned , this is tested for with the ISNA function , so we have in effect a double negative effect in that if there is a match and thus ISNA is not true we need to use the False result for there is a match.

That sounds as Clear as mud I hope you understand it.

Brian

That is very clear, I thought the double negative might be the case. Many many thanks for your help Brian