# 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

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.

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?

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.

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1