If function not working

[lmcc007]

I am trying to populate cells P, Q, R by:

1) Looking at the numbers in cells C, D, E to see if they match V1:AAE numbers.

2) If a match is found, the number above the match is entered into P, Q, R

3) If the number above has a # sign, DD, or TTT in the cell, I want a zero (0).

4) I don’t want to see #, DD, or TTT. I want to see only a zero.

5) Below is the code:

=IF(C4=$V$1, $V3, IF(C4=$W$1, $W3, IF(C4=$X$1, $X3, IF(C4=$Y$1, $Y3, IF(C4=$Z$1, $Z3, IF(C4=$AA$1, $AA3, IF(C4=$AB$1, $AB3, IF(C4=$AC$1, $AC3, IF(C4=$AD$1, $AD3, IF(C4=$AE$1, $AE3))))))))))

6) When I enter: =IF(V2="#", "0", V2) it works. But I can’t get it to work in the above code.

Help, please!

[kersplash]

Can you attach a sample workbook showing your mocked-up results.

Go Advanced -> Manage Attachments -> Upload

[lmcc007]

Okay, here it is.

[AliGW]

Try this as a starter in C4 copied across and down:

=SUBSTITUTE(INDEX($V3:$AE3,MATCH(C4,$V$1:$AE$1,0)),"#",0)

[lmcc007]

Thanks it is working for the # sign.

But, I don’t want to see #, DD, or TTT. I want to see only a zero. I changed the code to:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(INDEX($V2:$AE2,MATCH(C3,$V$1:$AE$1,0)),"#", 0), "DD", "0"), "TTT", "0")

it seems to be working. Did I do this correctly?

[AliGW]

Looks like it, except you don’t want the zeroes in inverted commas or they will be returned as text instead of numbers.

I did say that the formula was a starting point. I’m glad you worked out how to adapt it.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[jtakw]

Hi,

If you Only want numeric results from the "match", and if Any Text is returned, you just want 0 (zero), you can do this instead of SUBSTITUTEs:

Please Login or Register  to view this content.

[lmcc007]

Perfect, thanks a lot!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[jtakw]

If you found my post #7 above helpful, add rep would be appreciated.

Click the "Star" at lower left corner of posts you found helpful, Thank you.