VLOOKUP questions

[Guest]

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

 

[Pete_UK]

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

Hope this helps.

Pete

 

[Stan Brown]

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.

 

[Guest]

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),VLOOKUP(Customer!E2*1,salesrep,2,FALSE))

Please let me know if this was helpful.

Thanks,
Peggy

 

[Guest]

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),VLOOKUP(Customer!E2*1,salesrep,2,FALSE))

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

 

[Guest]

Hi Noncentz303,

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

Many thanks,
Peggy