IF Limitation?

[Michael168]

Because of IF limitations, I need some helps.
I need a macro to update one cell value by comparing 3 ells.valu
against 8 cells.value. (3*8=24 conditions)

In plain english it is as below:

To update the (J10)cell.value,I need to compare B10 with K9:R9, C1
with K9:R9 & D10 with K9:R9.

e.g. If B10=L9,C10=R9 & D10=N9 then J10=284

The text value (284) comes from column
K=1,L=2,M=3,N=4,O=5,P=6,Q=7,R=8

The macro will update the column (J) if columns B are not empty.

Thanks
Michae

 

[JWolf]

Try this formula instead in J10:
=MATCH(B10,K9:R9,0) & MATCH(C10,K9:R9,0) & MATCH(D10,K9:R9,0)

 

[JWolf]

=IF(ISNA(MATCH(B10,K9:R9,0)),B10,MATCH(B10,K9:R9,0)) &
IF(ISNA(MATCH(C10,K9:R9,0)),C10,MATCH(C10,K9:R9,0)) &
IF(ISNA(MATCH(D10,K9:R9,0)),D10,MATCH(D10,K9:R9,0))

will prevent an error if b10,c10,d10 are empty or no match is found,

 

[Ron Rosenfeld]

Because of IF limitations, I need some helps.
I need a macro to update one cell value by comparing 3 ells.value
against 8 cells.value. (3*8=24 conditions)

In plain english it is as below:

To update the (J10)cell.value,I need to compare B10 with K9:R9, C10
with K9:R9 & D10 with K9:R9.

e.g. If B10=L9,C10=R9 & D10=N9 then J10=284

The text value (284) comes from columns
K=1,L=2,M=3,N=4,O=5,P=6,Q=7,R=8

The macro will update the column (J) if columns B are not empty.

Thanks
Michael

You could do it with a formula. One such formula:

=IF(COUNTA(B1010)<3,"",CONCATENATE(
INDIRECT(B10),INDIRECT(C10),INDIRECT(D10)))


--ron