array formula - ignore part of string

**jonijay78** · 07-30-2010, 03:57 AM

I have this in my excel worksheet. I need to display specific charge for a specific place.
This is my sheet looks like.

Column A
A1: PLACE1
A2: PLACE2
A3: PLACE3

COLUMN B
B1: ADRESS PLACE1
B2: ADRESS PLACE2
B3: ADRESS PLACE3

COLUMN C
C1:CHARGE1($1)
C2:CHARGE2($2)
C3:CHARGE3($3)

COLUMN D
D1: INPUT PLACE

COLUMN E
E1: INPUT PLACE

COLULM F
F1: DISPLAY THE CHARGE

here is my working formula:
COLUMN F
F1:
{=INDEX($C$1:$C$3,MATCH(1,(E1=$A1:$A3)*(F1=$B$1:$B$3),0))}

here goes the problem..

sometimes when certain place is inputed in column E has the word "CITY"..if that happens, my formula does work anymore...my formula only works if you input place that is exactly the same spelling/spaces in column A. hope you get what i mean...thanks in advance.

please see attachment..thanks

**deadlyduck** · 07-30-2010, 04:54 AM

Could you use data validation for the user input cells so that they only choose the correct names from a dropdown list rather than type in the place name?

**jonijay78** · 07-30-2010, 05:55 AM

Originally Posted by deadlyduck

Could you use data validation for the user input cells so that they only choose the correct names from a dropdown list rather than type in the place name?

nope because this is already typed in a separate sheet....ill just have to paste to formula to display the rates. the problem is not all town is typed with or without the word "city"

**jonijay78** · 08-02-2010, 11:52 PM

anyone? please..

**teylyn** · 08-03-2010, 12:21 AM

try

Please Login or Register  to view this content.

**excel328** · 08-03-2010, 12:50 AM

non-array formula
=SUMPRODUCT(--($A$2:$A$539=VLOOKUP(IF(MID(E2,LEN(E2)-LEN("city")+1,4)="CITY",LEFT(E2,LEN(E2)-LEN("city")-1),E2),$A$2:$A$539,1,FALSE)),--($B$2:$B$539=VLOOKUP(F2,$B$2:$B$539,1,FALSE)),$C$2:$C$539)