# If text mathces column 1 range, display value in column 2 cell

1. ## If text mathces column 1 range, display value in column 2 cell

Help! I keep getting N/A or VALUE errors!

Columns and Cells Name

1. Activity Type Drop Down Options: Dme, O&m, blank (A3:A11)
2. Cost if activities are DME (B1)
3. Cost if activity if O&M (C1)

If all cells in 1 are DME, then 2 = \$1 and 3=\$0
If all cells in 1 are O&M, then 3=\$1 and 2=\$0
If cells in 1 are mixed O&M and DME, then 2=\$.5 and 3=\$.5
If all cells in 1 are blank, then 2=\$0 and 3=\$0  Register To Reply

2. B1: =IF(AND(A3:A11 = ""), 0, IF(AND(A3:A11 = "DME"), 1, IF(AND(A3:A11 = "O&M"), 0, 0.5) ) )

C1: =IF(AND(A3:A11 = ""), 0, IF(AND(A3:A11 = "O&M"), 1, IF(AND(A3:A11 = "DME"), 0, 0.5) ) )

Both of these are array formulas; they MUST be confirmed with Ctrl+Shift+Enter, not Enter. You'll know you did it correctly if curly braces appear around the formula in the Formula Bar; you cannot type in the braces directly.  Register To Reply

3. ## Getting an error

Using the formula format provided and ensuring I press Ctrl-Shift-Enter, I am getting an error.

My information was an example scenario - would the fact that my real data is accross spreadsheets cause the error?

Here is what I have:

=IF(AND('Worksheet1'!I39:I98="",0,IF(AND('Worksheet1'!I39:I98="DME"),1,IF(AND('Worksheet1'!I39:I98="O&M",0,0.5)))

So comparing to my example:
'Worksheet1'!I39:I98 = A1
'Worksheet2'!I2 = B1
'Worksheet3'! = C1

Another concern: for the range I39:I98, every other line will never have a value because they are used for styling purposes. Does this ruin the function?  Register To Reply

4. would the fact that my real data is accross spreadsheets cause the error?
No. Can you post a workbook?

... every other line will never have a value because they are used for styling purposes. Does this ruin the function?
Yes, it does.  Register To Reply

5. Please find the worksheet attached. Areas of interest are highlighted in BRIGHT ORANGE.

'GeneralInformation'!I39:I98 is the range with the option of DME or O&M

Part 1: If range 'GeneralInformation'!I39:I98 is all "DME" and some blank, then I want 'DME'!H28 to contain the full value of cell 'GeneralInformation'!I24

Part2: If the range 'GeneralInformation'!I39:I98 contains "DME" AND "O&M" and blank, then I want 'DME'!H28 to contain .5(half) the value of cell 'GeneralInformation'!I24'. The other .5(half) will then display in 'O&M'!H28.

Likewise, If range 'GeneralInformation'!I39:I98 is all "O&M" and some blank, then I want 'O&M'!H28 to contain the full value of cell 'GeneralInformation'!I24. Then follows the logic of Part 2 above.  Register To Reply