Formula Result Change when Workbook Sent by E-mail

[Rich F]

I've got a workbook with a couple of VLOOKUPs pointed to within the same
workbook. When I send the workbook via e-mail, the recipient gets
different/incorrect values where ever the Vlookup formulas are when they open
the file (punching F9 or any variation does nothing). When they send the
workbook back to me and I open it, the VLOOKUP values are incorrect also and
using any variation of F9 is fruitless, but if I go to the Raw Data page
(where the VLOOKUPs are pointed to) and filter on it, suddenly the VLOOKUPs
reflect the proper data--however, if the original recipient tries to do the
same thing, the VLOOKUPs still show incorrect info.

When I open the original workbook on my laptap, there are no issues--works
like it should.

I've sent hundreds of these kinds of the workbooks in the past and never had
this issue. This was a uniquely created workbook (meaning I didn't cut/copy
into an exisitng workbook).

Any ideas?

 

[Kevin B]

Is the VLOOKUP doing an exact match or an approximate match?

=VLOOKUP(LookUp_Table,Table_Array, Col_Index,[range_lookup])

if the optional [range lookup] is left empty or has a TRUE value the lookup
does an exact match, or approximate match if an exact match is not found.

Using a FALSE value for the optional range_lookup does an exact match only.

 

[Rich F]

Kevin,

Thanks for the question--it's an exact match I'm looking for. Here's
formula I'm using:

=IF(ISNA(VLOOKUP($G41,'SD Targets'!$A$2:$C$87,2,FALSE)),"",VLOOKUP($G41,'SD
Targets'!$A$2:$C$87,2,FALSE))

Wondering if using that clumsy IF(ISNA...) is making a difference? But I've
used that countless times in the past and it's never been an issue (by the
way, this is probably one of the smallest workbooks I've created in a
while...only 154kb).

Thanks again for your time!

--
Rich F

Is the VLOOKUP doing an exact match or an approximate match?

=VLOOKUP(LookUp_Table,Table_Array, Col_Index,[range_lookup])

if the optional [range lookup] is left empty or has a TRUE value the lookup
does an exact match, or approximate match if an exact match is not found.

Using a FALSE value for the optional range_lookup does an exact match only.
--
Kevin Backmann

I've got a workbook with a couple of VLOOKUPs pointed to within the same
workbook. When I send the workbook via e-mail, the recipient gets
different/incorrect values where ever the Vlookup formulas are when they open
the file (punching F9 or any variation does nothing). When they send the
workbook back to me and I open it, the VLOOKUP values are incorrect also and
using any variation of F9 is fruitless, but if I go to the Raw Data page
(where the VLOOKUPs are pointed to) and filter on it, suddenly the VLOOKUPs
reflect the proper data--however, if the original recipient tries to do the
same thing, the VLOOKUPs still show incorrect info.

When I open the original workbook on my laptap, there are no issues--works
like it should.

I've sent hundreds of these kinds of the workbooks in the past and never had
this issue. This was a uniquely created workbook (meaning I didn't cut/copy
into an exisitng workbook).

Any ideas?

 

[Kevin B]

I don't see anything wrong about your formula at all. And when I did a
variation of yours it worked just fine. I did a lookup where the lookup
column was not sorted in ascending order and another were it was sorted and
in both cases everything worked as planned.

I emailed myself the files, saved and opend them and nothing was wrong.

Here's my version of the formula. The only differences are that I used A1
as the source value for the lookup instead of G41 and I was too lazy to
recreate an 86 row lookup table and I settled for a 10 row one instead, other
than that there aren't any changes:

=IF(ISNA(VLOOKUP($A1,'SD Targets'!$A$1:$C$10,2,FALSE)),"",VLOOKUP($A1,'SD
Targets'!$A$1:$C$10,2,FALSE))

I'm stumped...

--
Kevin Backmann

Kevin,

Thanks for the question--it's an exact match I'm looking for. Here's
formula I'm using:

=IF(ISNA(VLOOKUP($G41,'SD Targets'!$A$2:$C$87,2,FALSE)),"",VLOOKUP($G41,'SD
Targets'!$A$2:$C$87,2,FALSE))

Wondering if using that clumsy IF(ISNA...) is making a difference? But I've
used that countless times in the past and it's never been an issue (by the
way, this is probably one of the smallest workbooks I've created in a
while...only 154kb).

Thanks again for your time!

--
Rich F

Is the VLOOKUP doing an exact match or an approximate match?

=VLOOKUP(LookUp_Table,Table_Array, Col_Index,[range_lookup])

if the optional [range lookup] is left empty or has a TRUE value the lookup
does an exact match, or approximate match if an exact match is not found.

Using a FALSE value for the optional range_lookup does an exact match only.
--
Kevin Backmann

I've got a workbook with a couple of VLOOKUPs pointed to within the same
workbook. When I send the workbook via e-mail, the recipient gets
different/incorrect values where ever the Vlookup formulas are when they open
the file (punching F9 or any variation does nothing). When they send the
workbook back to me and I open it, the VLOOKUP values are incorrect also and
using any variation of F9 is fruitless, but if I go to the Raw Data page
(where the VLOOKUPs are pointed to) and filter on it, suddenly the VLOOKUPs
reflect the proper data--however, if the original recipient tries to do the
same thing, the VLOOKUPs still show incorrect info.

When I open the original workbook on my laptap, there are no issues--works
like it should.

I've sent hundreds of these kinds of the workbooks in the past and never had
this issue. This was a uniquely created workbook (meaning I didn't cut/copy
into an exisitng workbook).

Any ideas?

 

[Rich F]

Kevin,

Thanks again, sir, for your time and efforts. Like I said, this is the
first time this has happened to me, so I'm thinking Excel must have hiccupped
when I created this file because I just created another file from scratch
exactly like the first one with updated raw data info and everything works
fine, even when I e-mail it.

Guess we can chalk it up to "sometimes applications have bad days...."

Again, thanks for your time--have a great one!
--
Rich F

I don't see anything wrong about your formula at all. And when I did a
variation of yours it worked just fine. I did a lookup where the lookup
column was not sorted in ascending order and another were it was sorted and
in both cases everything worked as planned.

I emailed myself the files, saved and opend them and nothing was wrong.

Here's my version of the formula. The only differences are that I used A1
as the source value for the lookup instead of G41 and I was too lazy to
recreate an 86 row lookup table and I settled for a 10 row one instead, other
than that there aren't any changes:

=IF(ISNA(VLOOKUP($A1,'SD Targets'!$A$1:$C$10,2,FALSE)),"",VLOOKUP($A1,'SD
Targets'!$A$1:$C$10,2,FALSE))

I'm stumped...

--
Kevin Backmann

Kevin,

Thanks for the question--it's an exact match I'm looking for. Here's
formula I'm using:

=IF(ISNA(VLOOKUP($G41,'SD Targets'!$A$2:$C$87,2,FALSE)),"",VLOOKUP($G41,'SD
Targets'!$A$2:$C$87,2,FALSE))

Wondering if using that clumsy IF(ISNA...) is making a difference? But I've
used that countless times in the past and it's never been an issue (by the
way, this is probably one of the smallest workbooks I've created in a
while...only 154kb).

Thanks again for your time!

--
Rich F

Is the VLOOKUP doing an exact match or an approximate match?

=VLOOKUP(LookUp_Table,Table_Array, Col_Index,[range_lookup])

if the optional [range lookup] is left empty or has a TRUE value the lookup
does an exact match, or approximate match if an exact match is not found.

Using a FALSE value for the optional range_lookup does an exact match only.
--
Kevin Backmann


:

I've got a workbook with a couple of VLOOKUPs pointed to within the same
workbook. When I send the workbook via e-mail, the recipient gets
different/incorrect values where ever the Vlookup formulas are when they open
the file (punching F9 or any variation does nothing). When they send the
workbook back to me and I open it, the VLOOKUP values are incorrect also and
using any variation of F9 is fruitless, but if I go to the Raw Data page
(where the VLOOKUPs are pointed to) and filter on it, suddenly the VLOOKUPs
reflect the proper data--however, if the original recipient tries to do the
same thing, the VLOOKUPs still show incorrect info.

When I open the original workbook on my laptap, there are no issues--works
like it should.

I've sent hundreds of these kinds of the workbooks in the past and never had
this issue. This was a uniquely created workbook (meaning I didn't cut/copy
into an exisitng workbook).

Any ideas?