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
--
bill gras

Maybe spaces in the value

=IF(R1!AF39=A2,IF(R1!N39=TRIM(B2),VLOOKUP(R1N39,TR IM(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)

"bill gras" wrote in message
...
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
--
bill gras

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

regards bill
--
bill gras


"Bob Phillips" wrote:

Maybe spaces in the value

=IF(R1!AF39=A2,IF(R1!N39=TRIM(B2),VLOOKUP(R1N39,TR IM(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)

"bill gras" wrote in message
...
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
--
bill gras

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)


"bill gras" wrote in message
...
Hi Bob
Thank you for your time
I done like you suggested but still returned " False "

regards bill
--
bill gras


"Bob Phillips" wrote:

Maybe spaces in the value

=IF(R1!AF39=A2,IF(R1!N39=TRIM(B2),VLOOKUP(R1N39,TR IM(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)

"bill gras" wrote in message
...
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
--
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
--
bill gras


"Bob Phillips" wrote:

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)


"bill gras" wrote in message
...
Hi Bob
Thank you for your time
I done like you suggested but still returned " False "

regards bill
--
bill gras


"Bob Phillips" wrote:

Maybe spaces in the value

=IF(R1!AF39=A2,IF(R1!N39=TRIM(B2),VLOOKUP(R1N39,TR IM(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)

"bill gras" wrote in message
...
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
--
bill gras

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)

"bill gras" wrote in message
...
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
--
bill gras


"Bob Phillips" wrote:

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)


"bill gras" wrote in message
...
Hi Bob
Thank you for your time
I done like you suggested but still returned " False "

regards bill
--
bill gras


"Bob Phillips" wrote:

Maybe spaces in the value


=IF(R1!AF39=A2,IF(R1!N39=TRIM(B2),VLOOKUP(R1N39,TR IM(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)

"bill gras" wrote in message
...
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
--
bill gras

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.
....

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.

Hi Bob and Harlan

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


"Harlan Grove" wrote:

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.
....

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.

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" wrote:

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.
....

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.