Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default VLOOKUP questions

I have a named range that I would like to search and then pull up a different
value. It looks like this.....

OLD NEW ( the range is named salesreps )

333 1045
444 1040
555 1024
888 1010
999 1001
AAA 1022
DLB 1037
EEE 1010

=(VLOOKUP(Customer!E2,salesrep,2,1)) <-- on my spreadsheet

the function that I wrote will take all the Alphabetical stuff but not any
numbers... what am i doing wrong
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default VLOOKUP questions

Make the last parameter of the VLOOKUP function 0 instead of 1 - then
it will look for an exact match.

Hope this helps.

Pete


On May 8, 11:56 pm, Noncentz303
wrote:
I have a named range that I would like to search and then pull up a different
value. It looks like this.....

OLD NEW ( the range is named salesreps )

333 1045
444 1040
555 1024
888 1010
999 1001
AAA 1022
DLB 1037
EEE 1010

=(VLOOKUP(Customer!E2,salesrep,2,1)) <-- on my spreadsheet

the function that I wrote will take all the Alphabetical stuff but not any
numbers... what am i doing wrong



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 524
Default VLOOKUP questions

Tue, 8 May 2007 15:56:07 -0700 from Noncentz303 <Noncentz303
@discussions.microsoft.com:

=(VLOOKUP(Customer!E2,salesrep,2,1)) <-- on my spreadsheet


That fourth argument, if present, is supposed to be TRUE or FALSE.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 72
Default VLOOKUP questions

Hi Noncentz303,

If the old column in salesreps has numbers that are text; you can check this
condition by entering the following worksheet formula: =istext(A2) where A2
is one of the cells that has a number, and checking to see if it is text or
not. If the number is text, then the formula will display "TRUE".

If so, try:
=VLOOKUP(TEXT(Customer!E2,"0"),salesrep,2,FALSE)

However if =istext(Customer!E2) is TRUE for a number, then try:

=IF(ISERROR(VLOOKUP(Customer!E2*1,salesrep,2,FALSE ))=TRUE,VLOOKUP(Customer!E2,salesrep,2,FALSE),VLOO KUP(Customer!E2*1,salesrep,2,FALSE))

Please let me know if this was helpful.

Thanks,
Peggy

"Noncentz303" wrote:

I have a named range that I would like to search and then pull up a different
value. It looks like this.....

OLD NEW ( the range is named salesreps )

333 1045
444 1040
555 1024
888 1010
999 1001
AAA 1022
DLB 1037
EEE 1010

=(VLOOKUP(Customer!E2,salesrep,2,1)) <-- on my spreadsheet

the function that I wrote will take all the Alphabetical stuff but not any
numbers... what am i doing wrong

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default VLOOKUP questions

YEEESSSS Thanks for the big helps guys

shepard that statement:
=IF(ISERROR(VLOOKUP(Customer!E2*1,salesrep,2,FALSE ))=TRUE,VLOOKUP(Customer!E2,salesrep,2,FALSE),VLOO KUP(Customer!E2*1,salesrep,2,FALSE))

worked like a charm... i didnt have my cells formated to text :)

"pshepard" wrote:

Hi Noncentz303,

If the old column in salesreps has numbers that are text; you can check this
condition by entering the following worksheet formula: =istext(A2) where A2
is one of the cells that has a number, and checking to see if it is text or
not. If the number is text, then the formula will display "TRUE".

If so, try:
=VLOOKUP(TEXT(Customer!E2,"0"),salesrep,2,FALSE)

However if =istext(Customer!E2) is TRUE for a number, then try:

=IF(ISERROR(VLOOKUP(Customer!E2*1,salesrep,2,FALSE ))=TRUE,VLOOKUP(Customer!E2,salesrep,2,FALSE),VLOO KUP(Customer!E2*1,salesrep,2,FALSE))

Please let me know if this was helpful.

Thanks,
Peggy

"Noncentz303" wrote:

I have a named range that I would like to search and then pull up a different
value. It looks like this.....

OLD NEW ( the range is named salesreps )

333 1045
444 1040
555 1024
888 1010
999 1001
AAA 1022
DLB 1037
EEE 1010

=(VLOOKUP(Customer!E2,salesrep,2,1)) <-- on my spreadsheet

the function that I wrote will take all the Alphabetical stuff but not any
numbers... what am i doing wrong



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 72
Default VLOOKUP questions

Hi Noncentz303,

Thank you for your feedback. Would you mind going to my post and indicating
YES that is was helpful?

Many thanks,
Peggy

"Noncentz303" wrote:

YEEESSSS Thanks for the big helps guys

shepard that statement:
=IF(ISERROR(VLOOKUP(Customer!E2*1,salesrep,2,FALSE ))=TRUE,VLOOKUP(Customer!E2,salesrep,2,FALSE),VLOO KUP(Customer!E2*1,salesrep,2,FALSE))

worked like a charm... i didnt have my cells formated to text :)

"pshepard" wrote:

Hi Noncentz303,

If the old column in salesreps has numbers that are text; you can check this
condition by entering the following worksheet formula: =istext(A2) where A2
is one of the cells that has a number, and checking to see if it is text or
not. If the number is text, then the formula will display "TRUE".

If so, try:
=VLOOKUP(TEXT(Customer!E2,"0"),salesrep,2,FALSE)

However if =istext(Customer!E2) is TRUE for a number, then try:

=IF(ISERROR(VLOOKUP(Customer!E2*1,salesrep,2,FALSE ))=TRUE,VLOOKUP(Customer!E2,salesrep,2,FALSE),VLOO KUP(Customer!E2*1,salesrep,2,FALSE))

Please let me know if this was helpful.

Thanks,
Peggy

"Noncentz303" wrote:

I have a named range that I would like to search and then pull up a different
value. It looks like this.....

OLD NEW ( the range is named salesreps )

333 1045
444 1040
555 1024
888 1010
999 1001
AAA 1022
DLB 1037
EEE 1010

=(VLOOKUP(Customer!E2,salesrep,2,1)) <-- on my spreadsheet

the function that I wrote will take all the Alphabetical stuff but not any
numbers... what am i doing wrong

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
2 questions Shawn iin Philly Excel Discussion (Misc queries) 1 October 6th 06 07:39 PM
Vlookup and few more questions :) [email protected] Excel Worksheet Functions 9 October 3rd 06 12:19 AM
VLOOKUP - 2 Questions Kathy - Lovullo Excel Worksheet Functions 1 April 14th 06 08:22 PM
VLOOKUP Questions nyys Excel Worksheet Functions 4 January 5th 06 03:41 PM
vlookup and calculation questions dtmd Excel Worksheet Functions 2 November 19th 05 11:34 PM


All times are GMT +1. The time now is 05:09 PM.

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"