Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Brentp97
 
Posts: n/a
Default How can I use "VLOOKUP" with cells containing both Text & Numbers?

I am attempting to create a cross reference between 2 spreadsheets. I am
currently using the "VLOOKUP" function to obtain my results. The problem
that I have run into is the values being compared & 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?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Niek Otten
 
Posts: n/a
Default How can I use "VLOOKUP" with cells containing both Text & Numbers?

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

--
Kind regards,

Niek Otten

"Brentp97" wrote in message
...
I am attempting to create a cross reference between 2 spreadsheets. I am
currently using the "VLOOKUP" function to obtain my results. The problem
that I have run into is the values being compared & 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?



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
CLR
 
Posts: n/a
Default How can I use "VLOOKUP" with cells containing both Text & Numbers?

Please post your VLOOKUP formula........

tks
Vaya con Dios,
Chuck, CABGx3



"Brentp97" wrote:

I am attempting to create a cross reference between 2 spreadsheets. I am
currently using the "VLOOKUP" function to obtain my results. The problem
that I have run into is the values being compared & 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?

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Brentp97
 
Posts: n/a
Default How can I use "VLOOKUP" with cells containing both Text & Numb

My formula reads:

=VLOOKUP($C:$C,'[POK AFE Detail.xls]AFE Detail'!B:D,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.


"Niek Otten" wrote:

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

--
Kind regards,

Niek Otten

"Brentp97" wrote in message
...
I am attempting to create a cross reference between 2 spreadsheets. I am
currently using the "VLOOKUP" function to obtain my results. The problem
that I have run into is the values being compared & 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?




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Niek Otten
 
Posts: n/a
Default How can I use "VLOOKUP" with cells containing both Text & Numb

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

"Brentp97" wrote in message
...
My formula reads:

=VLOOKUP($C:$C,'[POK AFE Detail.xls]AFE Detail'!B:D,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.


"Niek Otten" wrote:

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

--
Kind regards,

Niek Otten

"Brentp97" wrote in message
...
I am attempting to create a cross reference between 2 spreadsheets. I
am
currently using the "VLOOKUP" function to obtain my results. The
problem
that I have run into is the values being compared & 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?








  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Brentp97
 
Posts: n/a
Default How can I use "VLOOKUP" with cells containing both Text & Numb

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. A Col. B
37 formula
108B formula
201 formula

Spreadsheet # 2 (w/ data):

Col. A Col. B
37 30Y0A
37 61120
....
108A 647980
108B 647103
....
201 15F2C

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?

"Niek Otten" wrote:

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

"Brentp97" wrote in message
...
My formula reads:

=VLOOKUP($C:$C,'[POK AFE Detail.xls]AFE Detail'!B:D,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.


"Niek Otten" wrote:

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

--
Kind regards,

Niek Otten

"Brentp97" wrote in message
...
I am attempting to create a cross reference between 2 spreadsheets. I
am
currently using the "VLOOKUP" function to obtain my results. The
problem
that I have run into is the values being compared & 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?






  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Niek Otten
 
Posts: n/a
Default How can I use "VLOOKUP" with cells containing both Text & Numb

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

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:D,3,FALSE)

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

--
Kind regards,

Niek Otten


"Brentp97" wrote in message
...
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. A Col. B
37 formula
108B formula
201 formula

Spreadsheet # 2 (w/ data):

Col. A Col. B
37 30Y0A
37 61120
...
108A 647980
108B 647103
...
201 15F2C

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?

"Niek Otten" wrote:

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

"Brentp97" wrote in message
...
My formula reads:

=VLOOKUP($C:$C,'[POK AFE Detail.xls]AFE Detail'!B:D,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.


"Niek Otten" wrote:

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

--
Kind regards,

Niek Otten

"Brentp97" wrote in message
...
I am attempting to create a cross reference between 2 spreadsheets.
I
am
currently using the "VLOOKUP" function to obtain my results. The
problem
that I have run into is the values being compared & 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?








  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Niek Otten
 
Posts: n/a
Default How can I use "VLOOKUP" with cells containing both Text & Numb

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

Forget that


--
Kind regards,

Niek Otten

"Niek Otten" wrote in message
...
<Is VLOOKUP limited to searching only the left-most 3 digits?

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:D,3,FALSE)

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

--
Kind regards,

Niek Otten


"Brentp97" wrote in message
...
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. A Col. B
37 formula
108B formula
201 formula

Spreadsheet # 2 (w/ data):

Col. A Col. B
37 30Y0A
37 61120
...
108A 647980
108B 647103
...
201 15F2C

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?

"Niek Otten" wrote:

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

"Brentp97" wrote in message
...
My formula reads:

=VLOOKUP($C:$C,'[POK AFE Detail.xls]AFE Detail'!B:D,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.


"Niek Otten" wrote:

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

--
Kind regards,

Niek Otten

"Brentp97" wrote in message
...
I am attempting to create a cross reference between 2 spreadsheets.
I
am
currently using the "VLOOKUP" function to obtain my results. The
problem
that I have run into is the values being compared & 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?










Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Linked cells and text boxes Alexlondon11 Excel Discussion (Misc queries) 2 November 23rd 05 05:10 PM
stopping pasted text deliminate across multiple cells Paul from NZ Excel Discussion (Misc queries) 3 September 8th 05 06:06 AM
Counting Occurrence of Text within Text in Cells in Range. Jeremy N. Excel Worksheet Functions 1 September 8th 05 05:16 AM
Cells formated as numbers are calculating like text MM_BAM Excel Discussion (Misc queries) 4 July 7th 05 01:29 AM
Convert data of cells to any type: Number, Date&Time, Text Kevin Excel Discussion (Misc queries) 0 December 30th 04 07:55 AM


All times are GMT +1. The time now is 12:15 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"