|
Help with VLOOKUP()
I'm trying to engage
=VLOOKUP(C202,Grouping!$D$7:$E$377,2,FALSE) In sheet2 where my cell C202 is 230000 (a number) and in Column D on my Grouping Sheet I have a row (actually row #16 or Cell D16 is also 230000 (a number) My E16 has in it '230 This is what I need (as Text) But I'm getting the #N/A result. WHY? |
Help with VLOOKUP()
C202 and D16 *Do Not Match*!
They may look the same but *something* is different. Are either of these cells the results of calculations, where maybe your formatting is masking the *true* cell value? Are either of the cells imported, where there may be invisible characters present? Are both of these cells *true* XL recognizes numbers? Manually type in the same value in *both* cells, and then post back the results. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "JMay" wrote in message ... I'm trying to engage =VLOOKUP(C202,Grouping!$D$7:$E$377,2,FALSE) In sheet2 where my cell C202 is 230000 (a number) and in Column D on my Grouping Sheet I have a row (actually row #16 or Cell D16 is also 230000 (a number) My E16 has in it '230 This is what I need (as Text) But I'm getting the #N/A result. WHY? |
Help with VLOOKUP()
Are you certain both 230000 entries are numbers? Make the columns real wide
and do an edit clear formats. If one is left justified, it is a text entry, not a number. To solve, type a 1 in another cell, copy it, then highlight the text entries and do a edit, paste special, multiply. This will convert the text numbers to numbers. Make a backup first just in case! Bob Flanagan Macro Systems http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "JMay" wrote in message ... I'm trying to engage =VLOOKUP(C202,Grouping!$D$7:$E$377,2,FALSE) In sheet2 where my cell C202 is 230000 (a number) and in Column D on my Grouping Sheet I have a row (actually row #16 or Cell D16 is also 230000 (a number) My E16 has in it '230 This is what I need (as Text) But I'm getting the #N/A result. WHY? |
Help with VLOOKUP()
RagDyeR;
You're right - some of these number are imported or else the D column was pre-formatted as Text - Thanks for the help.. Jim "RagDyeR" wrote: C202 and D16 *Do Not Match*! They may look the same but *something* is different. Are either of these cells the results of calculations, where maybe your formatting is masking the *true* cell value? Are either of the cells imported, where there may be invisible characters present? Are both of these cells *true* XL recognizes numbers? Manually type in the same value in *both* cells, and then post back the results. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "JMay" wrote in message ... I'm trying to engage =VLOOKUP(C202,Grouping!$D$7:$E$377,2,FALSE) In sheet2 where my cell C202 is 230000 (a number) and in Column D on my Grouping Sheet I have a row (actually row #16 or Cell D16 is also 230000 (a number) My E16 has in it '230 This is what I need (as Text) But I'm getting the #N/A result. WHY? |
Help with VLOOKUP()
Appreciate the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JMay" wrote in message ... RagDyeR; You're right - some of these number are imported or else the D column was pre-formatted as Text - Thanks for the help.. Jim "RagDyeR" wrote: C202 and D16 *Do Not Match*! They may look the same but *something* is different. Are either of these cells the results of calculations, where maybe your formatting is masking the *true* cell value? Are either of the cells imported, where there may be invisible characters present? Are both of these cells *true* XL recognizes numbers? Manually type in the same value in *both* cells, and then post back the results. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "JMay" wrote in message ... I'm trying to engage =VLOOKUP(C202,Grouping!$D$7:$E$377,2,FALSE) In sheet2 where my cell C202 is 230000 (a number) and in Column D on my Grouping Sheet I have a row (actually row #16 or Cell D16 is also 230000 (a number) My E16 has in it '230 This is what I need (as Text) But I'm getting the #N/A result. WHY? |
Help with VLOOKUP()
Thanks Bob..
I spent an afternoon yesterday in IsitTextOrIsitANumber HELL. Setting up a recurring template utilizing the VLOOKUP() function I import A field from a Bank Download entitled Location Number. It can take different forms depending on various records we get from the bank -- The best record shows this field as a constant 10 digit number such as 0002300000 << and it naturally in it's context a text field (will never be used in any calculation). Yet when I download the Statement Off the web in this case it will come into my spreadsheet as 2300000. What I really need to do is strip the whole field down to a more practical reference, in this case 230 -- So there is where I am trying to convert (using the Vlookup function to Lookup the 2300000 in the master table where I map everything out and show a column for the 0002300000, also the 2300000 with a final column as 230. Without doing a full scale test of a cell (using either ISTEXT() or ISNUMBER() - Visually I can't tell what I've got, since just because it is left justified doesn't mean it's text (it could well be a number) Then there's this thing where you can select a number - go to Format, Cells, Text <<< Does this Format feature make the underlying content TEXT, or does it only Format it Just to make it look like text? I ended up utilizing the ' (apostrophe) several places to get things settled down - Although I know that next month when I import my data is going to once again come in as numerics - I set up a macro where I can Highlight these guys and run this Macro Sub AddApostrophe() For Each c In Selection c.Value = "'" & c.Value Next c End Sub Not sure this is what is need but FOR NOW it seems to be A WAY OUT (of my Trouble)... Thanks, Jim May "Bob Flanagan" wrote in message : Are you certain both 230000 entries are numbers? Make the columns real wide and do an edit clear formats. If one is left justified, it is a text entry, not a number. To solve, type a 1 in another cell, copy it, then highlight the text entries and do a edit, paste special, multiply. This will convert the text numbers to numbers. Make a backup first just in case! Bob Flanagan Macro Systems http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "JMay" wrote in message ... I'm trying to engage =VLOOKUP(C202,Grouping!$D$7:$E$377,2,FALSE) In sheet2 where my cell C202 is 230000 (a number) and in Column D on my Grouping Sheet I have a row (actually row #16 or Cell D16 is also 230000 (a number) My E16 has in it '230 This is what I need (as Text) But I'm getting the #N/A result. WHY? |
Help with VLOOKUP()
Say your C202 is a number keyed in by you ... 230.
Try this variation to your Lookup() formula: =VLOOKUP("*"&C202&"*",Grouping!$D$7:$E$377,2,0) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JMay" wrote in message ... Thanks Bob.. I spent an afternoon yesterday in IsitTextOrIsitANumber HELL. Setting up a recurring template utilizing the VLOOKUP() function I import A field from a Bank Download entitled Location Number. It can take different forms depending on various records we get from the bank -- The best record shows this field as a constant 10 digit number such as 0002300000 << and it naturally in it's context a text field (will never be used in any calculation). Yet when I download the Statement Off the web in this case it will come into my spreadsheet as 2300000. What I really need to do is strip the whole field down to a more practical reference, in this case 230 -- So there is where I am trying to convert (using the Vlookup function to Lookup the 2300000 in the master table where I map everything out and show a column for the 0002300000, also the 2300000 with a final column as 230. Without doing a full scale test of a cell (using either ISTEXT() or ISNUMBER() - Visually I can't tell what I've got, since just because it is left justified doesn't mean it's text (it could well be a number) Then there's this thing where you can select a number - go to Format, Cells, Text <<< Does this Format feature make the underlying content TEXT, or does it only Format it Just to make it look like text? I ended up utilizing the ' (apostrophe) several places to get things settled down - Although I know that next month when I import my data is going to once again come in as numerics - I set up a macro where I can Highlight these guys and run this Macro Sub AddApostrophe() For Each c In Selection c.Value = "'" & c.Value Next c End Sub Not sure this is what is need but FOR NOW it seems to be A WAY OUT (of my Trouble)... Thanks, Jim May "Bob Flanagan" wrote in message : Are you certain both 230000 entries are numbers? Make the columns real wide and do an edit clear formats. If one is left justified, it is a text entry, not a number. To solve, type a 1 in another cell, copy it, then highlight the text entries and do a edit, paste special, multiply. This will convert the text numbers to numbers. Make a backup first just in case! Bob Flanagan Macro Systems http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "JMay" wrote in message ... I'm trying to engage =VLOOKUP(C202,Grouping!$D$7:$E$377,2,FALSE) In sheet2 where my cell C202 is 230000 (a number) and in Column D on my Grouping Sheet I have a row (actually row #16 or Cell D16 is also 230000 (a number) My E16 has in it '230 This is what I need (as Text) But I'm getting the #N/A result. WHY? |
Help with VLOOKUP()
hummmmm;;; very interesting --I'll give it a shot.
thanks, Jim "Ragdyer" wrote in message : Say your C202 is a number keyed in by you ... 230. Try this variation to your Lookup() formula: =VLOOKUP("*"&C202&"*",Grouping!$D$7:$E$377,2,0) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JMay" wrote in message ... Thanks Bob.. I spent an afternoon yesterday in IsitTextOrIsitANumber HELL. Setting up a recurring template utilizing the VLOOKUP() function I import A field from a Bank Download entitled Location Number. It can take different forms depending on various records we get from the bank -- The best record shows this field as a constant 10 digit number such as 0002300000 << and it naturally in it's context a text field (will never be used in any calculation). Yet when I download the Statement Off the web in this case it will come into my spreadsheet as 2300000. What I really need to do is strip the whole field down to a more practical reference, in this case 230 -- So there is where I am trying to convert (using the Vlookup function to Lookup the 2300000 in the master table where I map everything out and show a column for the 0002300000, also the 2300000 with a final column as 230. Without doing a full scale test of a cell (using either ISTEXT() or ISNUMBER() - Visually I can't tell what I've got, since just because it is left justified doesn't mean it's text (it could well be a number) Then there's this thing where you can select a number - go to Format, Cells, Text <<< Does this Format feature make the underlying content TEXT, or does it only Format it Just to make it look like text? I ended up utilizing the ' (apostrophe) several places to get things settled down - Although I know that next month when I import my data is going to once again come in as numerics - I set up a macro where I can Highlight these guys and run this Macro Sub AddApostrophe() For Each c In Selection c.Value = "'" & c.Value Next c End Sub Not sure this is what is need but FOR NOW it seems to be A WAY OUT (of my Trouble)... Thanks, Jim May "Bob Flanagan" wrote in message : Are you certain both 230000 entries are numbers? Make the columns real wide and do an edit clear formats. If one is left justified, it is a text entry, not a number. To solve, type a 1 in another cell, copy it, then highlight the text entries and do a edit, paste special, multiply. This will convert the text numbers to numbers. Make a backup first just in case! Bob Flanagan Macro Systems http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "JMay" wrote in message ... I'm trying to engage =VLOOKUP(C202,Grouping!$D$7:$E$377,2,FALSE) In sheet2 where my cell C202 is 230000 (a number) and in Column D on my Grouping Sheet I have a row (actually row #16 or Cell D16 is also 230000 (a number) My E16 has in it '230 This is what I need (as Text) But I'm getting the #N/A result. WHY? |
| All times are GMT +1. The time now is 12:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com