Get SAME 2 CFs

[hsandeep]

I have input rangeA2:W50 & output range A53:W62
A2:A50 contains names
W2:W50 contains numerical values or blank

A53:A62 contains either of the ‘above’ names from A2:A50

In W53:W62, I am using INDEX & MATCH formula & get the analogous numerical values from W2:W50

W2:W50 contains CF example:

In W2’s Conditional Formatting Rules Manager 2 CFs are there
Formula: =ABL2=100 CF1
Formula: =ABM2=100 CF2

How to get the ‘same’ CFs in the range W53:W62?

 

[oldbrewer]

forget your specific problem and post a few rows of data together with your desired outcome - picture worth a thousand words and all that....

 

[hsandeep]

Will this do?
Sheet1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

KTK

PETER

JAMES

AMERICA

PETER

AMERICA

JAMES

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 2px"><col style="WIDTH: 2px"><col style="WIDTH: 2px"><col style="WIDTH: 2px"><col style="WIDTH: 2px"><col style="WIDTH: 2px"><col style="WIDTH: 2px"><col style="WIDTH: 2px"><col style="WIDTH: 2px"><col style="WIDTH: 2px"><col style="WIDTH: 2px"><col style="WIDTH: 2px"><col style="WIDTH: 2px"><col style="WIDTH: 2px"><col style="WIDTH: 2px"><col style="WIDTH: 2px"><col style="WIDTH: 2px"><col style="WIDTH: 2px"><col style="WIDTH: 2px"><col style="WIDTH: 2px"><col style="WIDTH: 2px"><col style="WIDTH: 64px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #ffff00, align: right"]285.23[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #008080, align: right"]289.6[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #008080, align: right"]355.44[/TD]

[TD="bgcolor: #cacaca, align: center"]46[/TD]

[TD="bgcolor: #cacaca, align: center"]47[/TD]

[TD="bgcolor: #cacaca, align: center"]48[/TD]

[TD="bgcolor: #cacaca, align: center"]49[/TD]

[TD="bgcolor: #cacaca, align: center"]50[/TD]

[TD="bgcolor: #ffff00, align: right"]785.78[/TD]

[TD="bgcolor: #cacaca, align: center"]51[/TD]

[TD="bgcolor: #cacaca, align: center"]52[/TD]

[TD="bgcolor: #cacaca, align: center"]53[/TD]

[TD="bgcolor: #008080, align: right"]289.6[/TD]

[TD="bgcolor: #cacaca, align: center"]54[/TD]

[TD="bgcolor: #ffff00, align: right"]785.78[/TD]

[TD="bgcolor: #cacaca, align: center"]55[/TD]

[TD="bgcolor: #008080, align: right"]355.44[/TD]

</tbody>

 

[barry houdini]

You could use VLOOKUP or INDEX/MATCH

Try this formula for CF1 for W53

=INDEX(ABL$2:ABL$50,MATCH(A53,A$2:A$50,0))=100

and similarly for CF2

=INDEX(ABM$2:ABM$50,MATCH(A53,A$2:A$50,0))=100

 

[oldbrewer]

Yes! it will do! you got a pretty quick reply !

 

[hsandeep]

Try this formula for CF1 for W53

=INDEX(ABL$2:ABL$50,MATCH(A53,A$2:A$50,0))=100

barry houdini, Your formula WORKS. But please help me with additional situation.....

Would like to have a formula in W53 which should give 'result' if EITHER OF columns ABL (ABL$2:ABL$50) or ABZ (ABZ$2:ABZ$50) 'matches' =100.

Sorry for my language, if unclear.

 

[barry houdini]

Perhaps just an OR....

=OR(INDEX(ABL$2:ABL$50,MATCH(A53,A$2:A$50,0))=100,INDEX(ABZ$2:ABZ$50,MATCH(A53,A$2:A$50,0))=100)

 

[hsandeep]

Perhaps just an OR....

=OR(INDEX(ABL$2:ABL$50,MATCH(A53,A$2:A$50,0))=100,INDEX(ABZ$2:ABZ$50,MATCH(A53,A$2:A$50,0))=100)

That WORKS.
barry houdini please a last extension needed....

In above formula: Please make a new formula: AND(INDEX/MATCH of ADJ2 is LESS THAN CZ2<cz2<cz2<cz2< font="">,</cz2<cz2<cz2<><cz2< font="">old formula) How to do it?</cz2<><cz2< font=""></cz2<>

 

[hsandeep]

I need a formula for CF1 for W53 which would be

AND((INDEX/MATCH ADJ$2:ADJ$50 IS LESS THAN CZ$2:CZ$50), (old formula) ) How to accomplish?

old formula=OR(INDEX(ABL$2:ABL$50,MATCH(A53,A$2:A$50,0))=100,INDEX(ABZ$2:ABZ$50,MATCH(A53,A$2:A$50,0))=100)