Return value based on multiple tests

[Weaseltron]

Hi all

This might be a simple request but I cant think of a neat way to do it.

Lets say I have the following table A:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Attachment Name
[/TD]
[TD]Value1
[/TD]
[TD]Value2
[/TD]
[TD] Value3
[/TD]
[TD]Value4
[/TD]
[TD]Value5
[/TD]
[/TR]
[TR]
[TD]file1
[/TD]
[TD]10
[/TD]
[TD]15
[/TD]
[TD]20
[/TD]
[TD]0
[/TD]
[TD]50
[/TD]
[/TR]
[TR]
[TD]file2
[/TD]
[TD]20
[/TD]
[TD]25
[/TD]
[TD]25
[/TD]
[TD]10
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]file3
[/TD]
[TD]25
[/TD]
[TD]50
[/TD]
[TD]50
[/TD]
[TD]5
[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]

and table B

[TABLE="width: 500"]
<tbody>[TR]
[TD]Client Name
[/TD]
[TD]Value1
[/TD]
[TD]Value2
[/TD]
[TD]Value3
[/TD]
[TD]Value4
[/TD]
[TD]Value5
[/TD]
[TD]Attachment to be used
[/TD]
[/TR]
[TR]
[TD]ClientA
[/TD]
[TD]10
[/TD]
[TD]15
[/TD]
[TD]20
[/TD]
[TD]0
[/TD]
[TD]50
[/TD]
[TD]

?
​

[/TD]
[/TR]
[TR]
[TD]ClientB
[/TD]
[TD]20
[/TD]
[TD]25[/TD]
[TD]25[/TD]
[TD]10
[/TD]
[TD]10[/TD]
[TD]

?​

[/TD]
[/TR]
[TR]
[TD]ClientC
[/TD]
[TD]30
[/TD]
[TD]15
[/TD]
[TD]15
[/TD]
[TD]15
[/TD]
[TD]15
[/TD]
[TD]

?
​

[/TD]
[/TR]
</tbody>[/TABLE]

What I am trying to do is find a formula to populate the "Attachment to be used" column.

This will need to compare the Value1, Value2 etc fields for each client to the Value1, Value2 etc fields for each attachment file and select the one that matches. It must be a 100% match for each value.

IF the values for a client match exactly the values for a particular attachment it should return that file name (ClientA = file1, ClientB = file2)
IF the vales for a client do not match exactly to an attachment it should return "no match" or something similar (ClientC)

Thanks in advance!

 

[Weaseltron]

To make it clearer, there could be multiple attachments with the same value for, say, Value1. The formula in table B will have to return the attachment name based on an exact match for every Value.

 

[jtakw]

Hi,

Here one way, Array formula to be confirm with CSE (Control, Shift, Enter), see instructions below:

Book1
	A	B	C	D	E	F	G
1	Attachment Name	Value1	Value2	Value3	Value4	Value5
2	file1	10	15	20	0	50
3	file2	20	25	25	10	10
4	file3	25	50	50	5	0
5
6
7	Client Name	Value1	Value2	Value3	Value4	Value5	Attachment to be used
8	ClientA	10	15	20	0	50	file1
9	ClientB	20	25	25	10	10	file2
10	ClientC	30	15	15	15	15	No Match
Sheet279

Cell Formulas
Range	Formula
G8	{=IFERROR(INDEX(A$2:A$4,MATCH(B8&C8&D8&E8&F8,B$2:B$4&C$2:C$4&D$2:D$4&E$2:E$4&F$2:F$4,0)),"No Match")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Formula copied down.

 

[Weaseltron]

Hi,

Here one way, Array formula to be confirm with CSE (Control, Shift, Enter), see instructions below:

	A	B	C	D	E	F	G
Attachment Name	Value1	Value2	Value3	Value4	Value5
file1
file2
file3
Client Name	Value1	Value2	Value3	Value4	Value5	Attachment to be used
ClientA	file1
ClientB	file2
ClientC	No Match

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]15[/TD]

</tbody>

Sheet279​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G8[/TH]
[TD="align: left"]{=IFERROR(INDEX(A$2:A$4,MATCH(B8&C8&D8&E8&F8,B$2:B$4&C$2:C$4&D$2:D$4&E$2:E$4&F$2:F$4,0)),"No Match")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Formula copied down.

Well thats absolutely glorious! Thank you so much, you've made my day!

 

[jtakw]

You're welcome.