match problem

B

bill gras

I need to match the date,match the names of city's , return the kilometers.
The date has different city's as a reference so I need to match the city
that is
entered in B2,B3,B4,B5 and so on , in my worksheet.
my formula is
=IF(R1!AF39=A2,IF(R1!N39=B2,VLOOKUP(R1N39,B2:C50,2,FALSE)))
it returns " FALSE "
when I change B2 in my worksheet from the word " alice springs " to R1!N39
(which is a result of a formula "alice springs") the formula works perfect
Can some one please help
Thanks
regards bill
 
B

Bob Phillips

Maybe spaces in the value

=IF(R1!AF39=A2,IF(R1!N39=TRIM(B2),VLOOKUP(R1N39,TRIM(B2:C50),2,FALSE)))

which needs to be array entered, commit with Ctrl-Shift-Enter, because of
the use of TRIM with a range. If this finds it, I suggest you remove
trailing spaces from the list in B2:B50, and revert to the original.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
B

bill gras

Hi Bob
Thank you for your time
I done like you suggested but still returned " False "

regards bill
 
B

Bob Phillips

Check the two cells with

=LEN(R1!N39)

=LEN(B2)

that should highlight the differences

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

bill gras

Hi Bob
=LEN(B2) returned " 13 "
=LEN(R1!N39) returned " 14 "
Can you tell me how and if I need to use the " TRIM " function.

regards bill
 
B

Bob Phillips

Bill,

You certainly need to fix it, as the fields are different. My TRIM
suggestion did not seem to work, so maybe the space is somewhere else, or it
is a non-breaking space (if imported from the web). Take a look at Dave
McRitichie's page on http://www.mvps.org/dmcritchie/excel/join.htm#trimall


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
H

Harlan Grove

bill gras wrote...
Hi Bob
=LEN(B2) returned " 13 "
=LEN(R1!N39) returned " 14 "
Can you tell me how and if I need to use the " TRIM " function.
Click to expand...
....

If your two strings are of different length, then they're necessarily
not equal. If there's no visual difference between them, then the most
likely difference is different numbers of space characters at the
beginning or end of one of the strings. However, there are two types of
space characters: breaking (ASCII) and
HTML: 
 nonbreaking. Excel's TRIM
function only removes the former. Check this with

=TRIM(B2)=TRIM(R1!N39)

and

=COUNTIF(R1!N39,"*"&B2&"*")=1

If the former returns FALSE but the latter returns TRUE, then it's
almost certain R1!N39 contains a leading or trailing nonbreaking space.
In which case, try

=TRIM(B2)=TRIM(SUBSTITUTE(R1!N39,CHAR(160)," "))

If that returns TRUE, you'll find it easiest to remove the nonbreaking
spaces from the range in the R1 worksheet. However, it it returns
FALSE, then there's some nastier difference between the two cells and
you're going to need to compare them character by character. If cells
X99:Z99 were blank, enter the following.

X99:
1

Y99:
=CODE(MID(B2,X99,1))

Z99:
=CODE(MID(R1!N39,X99,1))

Manually increment X99 until Y99 and Z99 show different values. X99
would give the position of first (leftmost) character that differs
between the two.
 
B

bill gras

Hi Bob and Harlan

Thank you both for your reply's
--
bill gras


Harlan Grove said:
bill gras wrote...
Hi Bob
=LEN(B2) returned " 13 "
=LEN(R1!N39) returned " 14 "
Can you tell me how and if I need to use the " TRIM " function.
Click to expand...
....

If your two strings are of different length, then they're necessarily
not equal. If there's no visual difference between them, then the most
likely difference is different numbers of space characters at the
beginning or end of one of the strings. However, there are two types of
space characters: breaking (ASCII) and
HTML: 
 nonbreaking. Excel's TRIM
function only removes the former. Check this with

=TRIM(B2)=TRIM(R1!N39)

and

=COUNTIF(R1!N39,"*"&B2&"*")=1

If the former returns FALSE but the latter returns TRUE, then it's
almost certain R1!N39 contains a leading or trailing nonbreaking space.
In which case, try

=TRIM(B2)=TRIM(SUBSTITUTE(R1!N39,CHAR(160)," "))

If that returns TRUE, you'll find it easiest to remove the nonbreaking
spaces from the range in the R1 worksheet. However, it it returns
FALSE, then there's some nastier difference between the two cells and
you're going to need to compare them character by character. If cells
X99:Z99 were blank, enter the following.

X99:
1

Y99:
=CODE(MID(B2,X99,1))

Z99:
=CODE(MID(R1!N39,X99,1))

Manually increment X99 until Y99 and Z99 show different values. X99
would give the position of first (leftmost) character that differs
between the two.
[/QUOTE]
Click to expand...
 
B

bill gras

Hi Harlan

I followed up from your last post and it showed me that " R1!N39 " (which
is a result of a formula), had a space entered before the name " alice
springs "
in my dump sheet . I was able correct this and all is well.

Thank you very much !
regards bill
bill gras


Harlan Grove said:
bill gras wrote...
Hi Bob
=LEN(B2) returned " 13 "
=LEN(R1!N39) returned " 14 "
Can you tell me how and if I need to use the " TRIM " function.
Click to expand...
....

If your two strings are of different length, then they're necessarily
not equal. If there's no visual difference between them, then the most
likely difference is different numbers of space characters at the
beginning or end of one of the strings. However, there are two types of
space characters: breaking (ASCII) and
HTML: 
 nonbreaking. Excel's TRIM
function only removes the former. Check this with

=TRIM(B2)=TRIM(R1!N39)

and

=COUNTIF(R1!N39,"*"&B2&"*")=1

If the former returns FALSE but the latter returns TRUE, then it's
almost certain R1!N39 contains a leading or trailing nonbreaking space.
In which case, try

=TRIM(B2)=TRIM(SUBSTITUTE(R1!N39,CHAR(160)," "))

If that returns TRUE, you'll find it easiest to remove the nonbreaking
spaces from the range in the R1 worksheet. However, it it returns
FALSE, then there's some nastier difference between the two cells and
you're going to need to compare them character by character. If cells
X99:Z99 were blank, enter the following.

X99:
1

Y99:
=CODE(MID(B2,X99,1))

Z99:
=CODE(MID(R1!N39,X99,1))

Manually increment X99 until Y99 and Z99 show different values. X99
would give the position of first (leftmost) character that differs
between the two.
[/QUOTE]
Click to expand...
 
Top