|
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 |
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 |
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/ |
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 |
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 |
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 |
| All times are GMT +1. The time now is 08:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com