software

I am attempting to create a cross reference between 2 spreadsheets. I am
currently using the quot;VLOOKUPquot; function to obtain my results. The problem
that I have run into is the values being compared amp; the desired output can
contain both numbers and text (some only contain numbers). The formula I am
using will work only on numbers. How can I get this to work for a
combination of both text and numbers?

What is your formula and what data do you use? What results did you get?

--
Kind regards,

Niek Otten

quot;Brentp97quot; gt; wrote in message
...
gt;I am attempting to create a cross reference between 2 spreadsheets. I am
gt; currently using the quot;VLOOKUPquot; function to obtain my results. The problem
gt; that I have run into is the values being compared amp; the desired output can
gt; contain both numbers and text (some only contain numbers). The formula I
gt; am
gt; using will work only on numbers. How can I get this to work for a
gt; combination of both text and numbers?
Please post your VLOOKUP formula........

tks
Vaya con Dios,
Chuck, CABGx3
quot;Brentp97quot; wrote:

gt; I am attempting to create a cross reference between 2 spreadsheets. I am
gt; currently using the quot;VLOOKUPquot; function to obtain my results. The problem
gt; that I have run into is the values being compared amp; the desired output can
gt; contain both numbers and text (some only contain numbers). The formula I am
gt; using will work only on numbers. How can I get this to work for a
gt; combination of both text and numbers?

My formula reads:

=VLOOKUP($C:$C,'[POK AFE Detail.xls]AFE Detail'!B,3)

$C$C contains values such as 3a, 101, 235f...

Column B contains the same values as above.

Column D on contains values such as 30Y0A, 64766899, 647598...

So basically, I am asking my formula to match exact values in 2 columns (on
seperate worksheets) and give a corresponding result from another column. It
seems to work if the columns being compared have 3 or less characters, but
not with 4 or more characters.

I hope this helps. It's hard to describe without viewing the file itself.quot;Niek Ottenquot; wrote:

gt; What is your formula and what data do you use? What results did you get?
gt;
gt; --
gt; Kind regards,
gt;
gt; Niek Otten
gt;
gt; quot;Brentp97quot; gt; wrote in message
gt; ...
gt; gt;I am attempting to create a cross reference between 2 spreadsheets. I am
gt; gt; currently using the quot;VLOOKUPquot; function to obtain my results. The problem
gt; gt; that I have run into is the values being compared amp; the desired output can
gt; gt; contain both numbers and text (some only contain numbers). The formula I
gt; gt; am
gt; gt; using will work only on numbers. How can I get this to work for a
gt; gt; combination of both text and numbers?
gt;
gt;
gt;

The first argument of VLOOKUP should be one cell, not an entire column. For
exact matches, you need a fourth argument which should be set to FALSE.
Now what are you trying to match with what and if there is a match, what
should happen?

--
Kind regards,

Niek Otten

quot;Brentp97quot; gt; wrote in message
...
gt; My formula reads:
gt;
gt; =VLOOKUP($C:$C,'[POK AFE Detail.xls]AFE Detail'!B,3)
gt;
gt; $C$C contains values such as 3a, 101, 235f...
gt;
gt; Column B contains the same values as above.
gt;
gt; Column D on contains values such as 30Y0A, 64766899, 647598...
gt;
gt; So basically, I am asking my formula to match exact values in 2 columns
gt; (on
gt; seperate worksheets) and give a corresponding result from another column.
gt; It
gt; seems to work if the columns being compared have 3 or less characters, but
gt; not with 4 or more characters.
gt;
gt; I hope this helps. It's hard to describe without viewing the file itself.
gt;
gt;
gt; quot;Niek Ottenquot; wrote:
gt;
gt;gt; What is your formula and what data do you use? What results did you get?
gt;gt;
gt;gt; --
gt;gt; Kind regards,
gt;gt;
gt;gt; Niek Otten
gt;gt;
gt;gt; quot;Brentp97quot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I am attempting to create a cross reference between 2 spreadsheets. I
gt;gt; gt;am
gt;gt; gt; currently using the quot;VLOOKUPquot; function to obtain my results. The
gt;gt; gt; problem
gt;gt; gt; that I have run into is the values being compared amp; the desired output
gt;gt; gt; can
gt;gt; gt; contain both numbers and text (some only contain numbers). The formula
gt;gt; gt; I
gt;gt; gt; am
gt;gt; gt; using will work only on numbers. How can I get this to work for a
gt;gt; gt; combination of both text and numbers?
gt;gt;
gt;gt;
gt;gt;
I want the formula to be generic, and it works, except when the values being
compared have 4 or more digits.

Here is a simplified example of my spreadsheet:

Spreadsheet #1 (w/ formula):

Col. ACol. B
37formula
108Bformula
201formula

Spreadsheet # 2 (w/ data):

Col. ACol. B
3730Y0A
3761120
....
108A647980
108B647103
....
20115F2C

When Col. A matches in each spreadsheet, I want my formula to find the
unique value listed in Col. B of Spreadsheet # 2 for the matching value
listed in Col. A.

Is VLOOKUP limited to searching only the left-most 3 digits?

quot;Niek Ottenquot; wrote:

gt; The first argument of VLOOKUP should be one cell, not an entire column. For
gt; exact matches, you need a fourth argument which should be set to FALSE.
gt; Now what are you trying to match with what and if there is a match, what
gt; should happen?
gt;
gt; --
gt; Kind regards,
gt;
gt; Niek Otten
gt;
gt; quot;Brentp97quot; gt; wrote in message
gt; ...
gt; gt; My formula reads:
gt; gt;
gt; gt; =VLOOKUP($C:$C,'[POK AFE Detail.xls]AFE Detail'!B,3)
gt; gt;
gt; gt; $C$C contains values such as 3a, 101, 235f...
gt; gt;
gt; gt; Column B contains the same values as above.
gt; gt;
gt; gt; Column D on contains values such as 30Y0A, 64766899, 647598...
gt; gt;
gt; gt; So basically, I am asking my formula to match exact values in 2 columns
gt; gt; (on
gt; gt; seperate worksheets) and give a corresponding result from another column.
gt; gt; It
gt; gt; seems to work if the columns being compared have 3 or less characters, but
gt; gt; not with 4 or more characters.
gt; gt;
gt; gt; I hope this helps. It's hard to describe without viewing the file itself.
gt; gt;
gt; gt;
gt; gt; quot;Niek Ottenquot; wrote:
gt; gt;
gt; gt;gt; What is your formula and what data do you use? What results did you get?
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt; Kind regards,
gt; gt;gt;
gt; gt;gt; Niek Otten
gt; gt;gt;
gt; gt;gt; quot;Brentp97quot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt;I am attempting to create a cross reference between 2 spreadsheets. I
gt; gt;gt; gt;am
gt; gt;gt; gt; currently using the quot;VLOOKUPquot; function to obtain my results. The
gt; gt;gt; gt; problem
gt; gt;gt; gt; that I have run into is the values being compared amp; the desired output
gt; gt;gt; gt; can
gt; gt;gt; gt; contain both numbers and text (some only contain numbers). The formula
gt; gt;gt; gt; I
gt; gt;gt; gt; am
gt; gt;gt; gt; using will work only on numbers. How can I get this to work for a
gt; gt;gt; gt; combination of both text and numbers?
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

lt;Is VLOOKUP limited to searching only the left-most 3 digits?gt;

No. But you probably have text and numbers mixed. 37, for example, can be a
number or can be text and for Excel they're different.
You can check with the ISTEXT() or ISNUMBER() function. Since part of your
data is text, make it all text.

Your formula is still incorrect; the 1st argument must be only one cell and
you need a 4th argument, set to FALSE.
Then you can copy down your formula as far as your data goes, like in

=VLOOKUP(C1,'[POK AFE Detail.xls]AFE Detail'!B,3,FALSE)

Note that the 3 should be a 2 if your data is in column B, like in your
example.

--
Kind regards,

Niek Ottenquot;Brentp97quot; gt; wrote in message
...
gt;I want the formula to be generic, and it works, except when the values
gt;being
gt; compared have 4 or more digits.
gt;
gt; Here is a simplified example of my spreadsheet:
gt;
gt; Spreadsheet #1 (w/ formula):
gt;
gt; Col. A Col. B
gt; 37 formula
gt; 108B formula
gt; 201 formula
gt;
gt; Spreadsheet # 2 (w/ data):
gt;
gt; Col. A Col. B
gt; 37 30Y0A
gt; 37 61120
gt; ...
gt; 108A 647980
gt; 108B 647103
gt; ...
gt; 201 15F2C
gt;
gt; When Col. A matches in each spreadsheet, I want my formula to find the
gt; unique value listed in Col. B of Spreadsheet # 2 for the matching value
gt; listed in Col. A.
gt;
gt; Is VLOOKUP limited to searching only the left-most 3 digits?
gt;
gt; quot;Niek Ottenquot; wrote:
gt;
gt;gt; The first argument of VLOOKUP should be one cell, not an entire column.
gt;gt; For
gt;gt; exact matches, you need a fourth argument which should be set to FALSE.
gt;gt; Now what are you trying to match with what and if there is a match, what
gt;gt; should happen?
gt;gt;
gt;gt; --
gt;gt; Kind regards,
gt;gt;
gt;gt; Niek Otten
gt;gt;
gt;gt; quot;Brentp97quot; gt; wrote in message
gt;gt; ...
gt;gt; gt; My formula reads:
gt;gt; gt;
gt;gt; gt; =VLOOKUP($C:$C,'[POK AFE Detail.xls]AFE Detail'!B,3)
gt;gt; gt;
gt;gt; gt; $C$C contains values such as 3a, 101, 235f...
gt;gt; gt;
gt;gt; gt; Column B contains the same values as above.
gt;gt; gt;
gt;gt; gt; Column D on contains values such as 30Y0A, 64766899, 647598...
gt;gt; gt;
gt;gt; gt; So basically, I am asking my formula to match exact values in 2 columns
gt;gt; gt; (on
gt;gt; gt; seperate worksheets) and give a corresponding result from another
gt;gt; gt; column.
gt;gt; gt; It
gt;gt; gt; seems to work if the columns being compared have 3 or less characters,
gt;gt; gt; but
gt;gt; gt; not with 4 or more characters.
gt;gt; gt;
gt;gt; gt; I hope this helps. It's hard to describe without viewing the file
gt;gt; gt; itself.
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; quot;Niek Ottenquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; What is your formula and what data do you use? What results did you
gt;gt; gt;gt; get?
gt;gt; gt;gt;
gt;gt; gt;gt; --
gt;gt; gt;gt; Kind regards,
gt;gt; gt;gt;
gt;gt; gt;gt; Niek Otten
gt;gt; gt;gt;
gt;gt; gt;gt; quot;Brentp97quot; gt; wrote in message
gt;gt; gt;gt; ...
gt;gt; gt;gt; gt;I am attempting to create a cross reference between 2 spreadsheets.
gt;gt; gt;gt; gt;I
gt;gt; gt;gt; gt;am
gt;gt; gt;gt; gt; currently using the quot;VLOOKUPquot; function to obtain my results. The
gt;gt; gt;gt; gt; problem
gt;gt; gt;gt; gt; that I have run into is the values being compared amp; the desired
gt;gt; gt;gt; gt; output
gt;gt; gt;gt; gt; can
gt;gt; gt;gt; gt; contain both numbers and text (some only contain numbers). The
gt;gt; gt;gt; gt; formula
gt;gt; gt;gt; gt; I
gt;gt; gt;gt; gt; am
gt;gt; gt;gt; gt; using will work only on numbers. How can I get this to work for a
gt;gt; gt;gt; gt; combination of both text and numbers?
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;gt;
lt;Note that the 3 should be a 2 if your data is in column B, like in your
example.gt;

Forget that--
Kind regards,

Niek Otten

quot;Niek Ottenquot; gt; wrote in message
...
gt; lt;Is VLOOKUP limited to searching only the left-most 3 digits?gt;
gt;
gt; No. But you probably have text and numbers mixed. 37, for example, can be
gt; a number or can be text and for Excel they're different.
gt; You can check with the ISTEXT() or ISNUMBER() function. Since part of your
gt; data is text, make it all text.
gt;
gt; Your formula is still incorrect; the 1st argument must be only one cell
gt; and you need a 4th argument, set to FALSE.
gt; Then you can copy down your formula as far as your data goes, like in
gt;
gt; =VLOOKUP(C1,'[POK AFE Detail.xls]AFE Detail'!B,3,FALSE)
gt;
gt; Note that the 3 should be a 2 if your data is in column B, like in your
gt; example.
gt;
gt; --
gt; Kind regards,
gt;
gt; Niek Otten
gt;
gt;
gt; quot;Brentp97quot; gt; wrote in message
gt; ...
gt;gt;I want the formula to be generic, and it works, except when the values
gt;gt;being
gt;gt; compared have 4 or more digits.
gt;gt;
gt;gt; Here is a simplified example of my spreadsheet:
gt;gt;
gt;gt; Spreadsheet #1 (w/ formula):
gt;gt;
gt;gt; Col. A Col. B
gt;gt; 37 formula
gt;gt; 108B formula
gt;gt; 201 formula
gt;gt;
gt;gt; Spreadsheet # 2 (w/ data):
gt;gt;
gt;gt; Col. A Col. B
gt;gt; 37 30Y0A
gt;gt; 37 61120
gt;gt; ...
gt;gt; 108A 647980
gt;gt; 108B 647103
gt;gt; ...
gt;gt; 201 15F2C
gt;gt;
gt;gt; When Col. A matches in each spreadsheet, I want my formula to find the
gt;gt; unique value listed in Col. B of Spreadsheet # 2 for the matching value
gt;gt; listed in Col. A.
gt;gt;
gt;gt; Is VLOOKUP limited to searching only the left-most 3 digits?
gt;gt;
gt;gt; quot;Niek Ottenquot; wrote:
gt;gt;
gt;gt;gt; The first argument of VLOOKUP should be one cell, not an entire column.
gt;gt;gt; For
gt;gt;gt; exact matches, you need a fourth argument which should be set to FALSE.
gt;gt;gt; Now what are you trying to match with what and if there is a match, what
gt;gt;gt; should happen?
gt;gt;gt;
gt;gt;gt; --
gt;gt;gt; Kind regards,
gt;gt;gt;
gt;gt;gt; Niek Otten
gt;gt;gt;
gt;gt;gt; quot;Brentp97quot; gt; wrote in message
gt;gt;gt; ...
gt;gt;gt; gt; My formula reads:
gt;gt;gt; gt;
gt;gt;gt; gt; =VLOOKUP($C:$C,'[POK AFE Detail.xls]AFE Detail'!B,3)
gt;gt;gt; gt;
gt;gt;gt; gt; $C$C contains values such as 3a, 101, 235f...
gt;gt;gt; gt;
gt;gt;gt; gt; Column B contains the same values as above.
gt;gt;gt; gt;
gt;gt;gt; gt; Column D on contains values such as 30Y0A, 64766899, 647598...
gt;gt;gt; gt;
gt;gt;gt; gt; So basically, I am asking my formula to match exact values in 2
gt;gt;gt; gt; columns
gt;gt;gt; gt; (on
gt;gt;gt; gt; seperate worksheets) and give a corresponding result from another
gt;gt;gt; gt; column.
gt;gt;gt; gt; It
gt;gt;gt; gt; seems to work if the columns being compared have 3 or less characters,
gt;gt;gt; gt; but
gt;gt;gt; gt; not with 4 or more characters.
gt;gt;gt; gt;
gt;gt;gt; gt; I hope this helps. It's hard to describe without viewing the file
gt;gt;gt; gt; itself.
gt;gt;gt; gt;
gt;gt;gt; gt;
gt;gt;gt; gt; quot;Niek Ottenquot; wrote:
gt;gt;gt; gt;
gt;gt;gt; gt;gt; What is your formula and what data do you use? What results did you
gt;gt;gt; gt;gt; get?
gt;gt;gt; gt;gt;
gt;gt;gt; gt;gt; --
gt;gt;gt; gt;gt; Kind regards,
gt;gt;gt; gt;gt;
gt;gt;gt; gt;gt; Niek Otten
gt;gt;gt; gt;gt;
gt;gt;gt; gt;gt; quot;Brentp97quot; gt; wrote in message
gt;gt;gt; gt;gt; ...
gt;gt;gt; gt;gt; gt;I am attempting to create a cross reference between 2 spreadsheets.
gt;gt;gt; gt;gt; gt;I
gt;gt;gt; gt;gt; gt;am
gt;gt;gt; gt;gt; gt; currently using the quot;VLOOKUPquot; function to obtain my results. The
gt;gt;gt; gt;gt; gt; problem
gt;gt;gt; gt;gt; gt; that I have run into is the values being compared amp; the desired
gt;gt;gt; gt;gt; gt; output
gt;gt;gt; gt;gt; gt; can
gt;gt;gt; gt;gt; gt; contain both numbers and text (some only contain numbers). The
gt;gt;gt; gt;gt; gt; formula
gt;gt;gt; gt;gt; gt; I
gt;gt;gt; gt;gt; gt; am
gt;gt;gt; gt;gt; gt; using will work only on numbers. How can I get this to work for a
gt;gt;gt; gt;gt; gt; combination of both text and numbers?
gt;gt;gt; gt;gt;
gt;gt;gt; gt;gt;
gt;gt;gt; gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;
gt;