SOLVED

Multiple criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-2817483%22%20slang%3D%22en-US%22%3EMultiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2817483%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20%22Trial%22%20file%20with%20Input%2C%20Data%20and%20Output%20worksheets.%20In%20the%20yellow%20area%2C%20I%20want%20to%20have%20filled%20same%20as%20Output%20file%20but%20with%20formulas.%20The%20formula%20should%20be%20provide%20result%20if%20one%20or%20more%20condition%20matches%20based%20on%26nbsp%3B%3CSPAN%3Ecompany%20ID%20(column%20B)%20%2C%20domain%20(column%20D)%20or%20Name%20(column%20C).%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2817483%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2817655%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2817655%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F577386%22%20target%3D%22_blank%22%3E%40Dharmendra_Bharwad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIFERROR(%0A%20%20%20INDEX(Data!%24A%242%3A%24A%245%2CMATCH(%24B2%2CData!%24G%242%3A%24G%245%2C0))%2C%0A%20IFERROR(%0A%20%20%20INDEX(Data!%24A%242%3A%24A%245%2CMATCH(%24C2%2CData!%24B%242%3A%24B%245%2C0))%2C%0A%20IFERROR(%0A%20%20%20INDEX(Data!%24A%242%3A%24A%245%2CMATCH(%24D2%2CData!%24D%242%3A%24D%245%2C0))%2C%22%22))%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Efor%20Company%20ID%2C%20other%20fields%20are%20similar.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2818599%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2818599%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20working%20out%20the%20formula.%20However%2C%20the%20formula%20reverts%20the%20same%20result%20for%20Company%20id%20and%20other%20fields.%20It%20is%20not%20dynamic.%20Can%20you%20please%20re-work%20on%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2819399%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2819399%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F577386%22%20target%3D%22_blank%22%3E%40Dharmendra_Bharwad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESorry%2C%20when%20I%20didn't%20catch%20what%20is%20required.%3C%2FP%3E%0A%3CP%3EHere%20is%20desired%20output%20(Output%20sheet)%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20339px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F315685iC9F7BE57C317AEB4%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EThat%20is%20what%20formula%20returns%20(Input%20sheet)%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20287px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F315686i8DA39A141C4E395C%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EI%20see%20no%20difference.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2819413%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2819413%22%20slang%3D%22en-US%22%3EPlease%20indicate%20which%20excel%20version%20you%20have%20so%20the%20formulas%20used%20in%20the%20solution%20can%20be%20maximized%20to%20its%20full%20potential%2Fease.%3CBR%20%2F%3E%3CBR%20%2F%3Echeers%3C%2FLINGO-BODY%3E
Contributor

Hello,

 

I have attached a "Trial" file with Input, Data and Output worksheets. In the yellow area, I want to have filled same as Output file but with formulas. The formula should be provide result if one or more condition matches based on company ID (column B) , domain (column D) or Name (column C).

7 Replies
best response confirmed by Dharmendra_Bharwad (Contributor)
Solution

@Dharmendra_Bharwad 

That could be

=IFERROR(
   INDEX(Data!$A$2:$A$5,MATCH($B2,Data!$G$2:$G$5,0)),
 IFERROR(
   INDEX(Data!$A$2:$A$5,MATCH($C2,Data!$B$2:$B$5,0)),
 IFERROR(
   INDEX(Data!$A$2:$A$5,MATCH($D2,Data!$D$2:$D$5,0)),""))
)

for Company ID, other fields are similar.

@Sergei Baklan 

 

Thanks for working out the formula. However, the formula reverts the same result for Company id and other fields. It is not dynamic. Can you please re-work on this?

@Dharmendra_Bharwad 

Sorry, when I didn't catch what is required.

Here is desired output (Output sheet)

image.png

That is what formula returns (Input sheet)

image.png

I see no difference.

Please indicate which excel version you have so the formulas used in the solution can be maximized to its full potential/ease.

cheers

@Sergei Baklan 

 

When I copy and paste the formula, it shows same value in other column

Dharmendra_Bharwad_1-1633600175669.png

 

And I need this to be achieved as an output:

Dharmendra_Bharwad_2-1633600237237.png

Hop this clears.

 

I have resolved this on my own. Thanks for the help. However, I will mark this as best response.

@Dharmendra_Bharwad 

Thank you for the feedback. You need to change the references, not just copy/paste.