# Need Help with Match, VLookup, or IF when using arrays

#### Freebirdrld

##### New Member
Hello,

I am trying to get one cell to show a phrase/text, if ANY of the cells within an array in a column display some other text.
I need this to work even with empty/blank cells in the array.
If none of the cells in the column array match the phrase, then the result cell should stay blank.

So, let's say cells A1 to A5 are as follows:
A1-Trunk Line PRI Handoff
A2-(blank/empty)
A3-Toll Free Number
A4-(blank/empty)
A5-(blank/empty)

And I want cell A6 to state: "All long distance billed at \$.015/min" IF ANY CELL IN A1 through A5 says "Trunk Line PRI Handoff" (which cell A1 does in my example). If NONE of the cells in the range matched the phrase, A6 would remain empty/blank.

I want to use the same formula for another cell with different criteria, so lets say cell A7. IF ANY CELL in A1 through A5 says "Toll Free Number" , then cell A7 should say "\$0.03 per minute", IF none of the cells in the range say "Toll Free Number" then cell A7 remains empty/blank.

I've tried to use Match, IF, and vLookup, but can't seem to work it out with the one fixed cell looking at a range, and both criteria and result being Text and not values. It's driving me nuts and I've wasted so much time, I'm hoping someone here can save the day and help me out!! Thanks!

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Try this in A6:

Code:
``=IF(COUNTIF(\$A\$1:\$A\$5,"Trunk Line PRI Handoff")>0,"All long distance billed at \$.015/min","")``

and try this in A7:

Code:
``=IF(COUNTIF(\$A\$1:\$A\$5,"Toll Free Number")>0,"\$0.03 per minute","")``

Try this in A6:

Code:
``=IF(COUNTIF(\$A\$1:\$A\$5,"Trunk Line PRI Handoff")>0,"All long distance billed at \$.015/min","")``

and try this in A7:

Code:
``=IF(COUNTIF(\$A\$1:\$A\$5,"Toll Free Number")>0,"\$0.03 per minute","")``

Thanks! I'll try it and let you know how it turns out.

Try this in A6:

Code:
``=IF(COUNTIF(\$A\$1:\$A\$5,"Trunk Line PRI Handoff")>0,"All long distance billed at \$.015/min","")``

and try this in A7:

Code:
``=IF(COUNTIF(\$A\$1:\$A\$5,"Toll Free Number")>0,"\$0.03 per minute","")``

Pjpassa-worked perfectly! thank you, thank you, thank you! I was getting so frustrated, so I really appreciate you taking the time to help out!

Replies
2
Views
90
Replies
2
Views
189
Replies
23
Views
2K
Replies
2
Views
418
Replies
6
Views
245

1,207,012
Messages
6,076,148
Members
446,187
Latest member
LMill

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back