Lookup - Formatting of numbers

I currently have a lookup field in my spreadsheet that looks up the
price of a product by product number.

Although the lookup usually works there are occasions when it returnes
the N/A even tho the product number is in the lookup table.

If I copy the product reference from the lookup table and paste it into
the first spreadsheet, the lookup then gives the correct price.  

I've tried formatting the product reference field in the spreadsheet
and in the lookup table to try to get them to match, but i still get
these errors. 

Can anyone shed any light?

thanks

Gill


---
Message posted from http://www.ExcelForum.com/

0
8/2/2004 3:36:10 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
462 Views

Similar Articles

[PageSpeed] 17

Most of the time, adding the word "False" at the end of the vlooku
formula helps me keep my sanity.

For example, the formula will look like this:

=vlookup(B1,$A$1:$A$100,2,false)

... hope this will help you.


GRS wrote:
> *I currently have a lookup field in my spreadsheet that looks up th
> price of a product by product number.
> 
> Although the lookup usually works there are occasions when i
> returnes the N/A even tho the product number is in the lookup table.
> 
> If I copy the product reference from the lookup table and paste i
> into the first spreadsheet, the lookup then gives the correct price.
> 
> 
> I've tried formatting the product reference field in the spreadshee
> and in the lookup table to try to get them to match, but i still ge
> these errors. 
> 
> Can anyone shed any light?
> 
> thanks
> 
> Gill 

--
Message posted from http://www.ExcelForum.com

0
8/2/2004 3:43:49 PM
Some of your product numbers are probably entered as text (this can 
happen when the cell is preformatted as text, or when data is pasted 
from a web page). To coerce them back to numbers, Copy a blank cell, 
select your product numbers, choose Edit/Paste Special, and select the 
Add and Values radio buttons. Click OK.


In article <GRS.1adb28@excelforum-nospam.com>,
 GRS <<GRS.1adb28@excelforum-nospam.com>> wrote:

> I currently have a lookup field in my spreadsheet that looks up the
> price of a product by product number.
> 
> Although the lookup usually works there are occasions when it returnes
> the N/A even tho the product number is in the lookup table.
> 
> If I copy the product reference from the lookup table and paste it into
> the first spreadsheet, the lookup then gives the correct price.  
> 
> I've tried formatting the product reference field in the spreadsheet
> and in the lookup table to try to get them to match, but i still get
> these errors. 
> 
> Can anyone shed any light?
0
jemcgimpsey (6723)
8/2/2004 3:46:04 PM
thanks for the reply.  My lookup is like this...

=INDEX(Prices!$B$1:$D$735, MATCH(D468,Prices!$B$1:$B$735,), MATCH("Cos
Price",Prices!$B$1:$D$1,))


humm...maybe it isn't a lookup then????

Where would I put the 'false' and regain MY sanity!

thanks

Gil

--
Message posted from http://www.ExcelForum.com

0
8/2/2004 3:52:05 PM
Based on your notations, the following are what I can conclude:

1. Your table is in another Worksheet named "Prices"
2. Your table range is B1:D735
3. The product number to be looked up is in cell D468

Furthermore, I will assume that the price you want your formula to giv
you is in (Prices!) column D.

Based on the above criteria, see if the following formula will help yo
in any way:

=vlookup(D468,Prices!$B$1:$D$735,3,false)

The "false" is always the last argument in the vlookup formula.


GRS wrote:
> *thanks for the reply.  My lookup is like this...
> 
> =INDEX(Prices!$B$1:$D$735, MATCH(D468,Prices!$B$1:$B$735,)
> MATCH("Cost Price",Prices!$B$1:$D$1,))
> 
> 
> humm...maybe it isn't a lookup then????
> 
> Where would I put the 'false' and regain MY sanity!
> 
> thanks
> 
> Gill 

--
Message posted from http://www.ExcelForum.com

0
8/2/2004 7:55:08 PM
MATCH is similar in principle to VLOOKUP. You would use FALSE or 0 as the 3rd
argument in the MATCH formulas. But your problem is most likely the table
entries or D468 contains text rather than a number.

On Mon, 2 Aug 2004 10:52:05 -0500, GRS <<GRS.1adbsr@excelforum-nospam.com>>
wrote:

>thanks for the reply.  My lookup is like this...
>
>=INDEX(Prices!$B$1:$D$735, MATCH(D468,Prices!$B$1:$B$735,), MATCH("Cost
>Price",Prices!$B$1:$D$1,))
>
>
>humm...maybe it isn't a lookup then????
>
>Where would I put the 'false' and regain MY sanity!
>
>thanks
>
>Gill
>
>
>---
>Message posted from http://www.ExcelForum.com/

0
anonymous (74717)
8/2/2004 8:37:47 PM
And you actually have 0's (or falses) in your formulas:
MATCH(D468,Prices!$B$1:$B$735,)

That last comma doesn't have anything after it.  

You get the same effect with:

=IF(A1="a","hi",)

So, I, too would look for differences in either spelling (or text v. numbers).


"GRS <" wrote:
> 
> thanks for the reply.  My lookup is like this...
> 
> =INDEX(Prices!$B$1:$D$735, MATCH(D468,Prices!$B$1:$B$735,), MATCH("Cost
> Price",Prices!$B$1:$D$1,))
> 
> humm...maybe it isn't a lookup then????
> 
> Where would I put the 'false' and regain MY sanity!
> 
> thanks
> 
> Gill
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
8/2/2004 10:31:32 PM
Reply:

Similar Artilces:

Table relationships and lookups
Hi guys, I may be a little over my head, I've had some experience in creating simple access db's. however this one will be extremely complicated as far as I can tell. Some backround info - i've got an excel spreadsheet currently that i would like to convert to Access. The spreadsheet does multiple lookups and calucations. This is for a Soccer club that i run to maintain roster information, dollars, scheduling and stats. I'm currently working on the scheduling pience. Here's what I have so far. tables. Club - Lists the teams in the club, home field name and ...

phone number disappearing
I have Money 2003 Deluxe. Lately when I use ePay the last digit of the payees phone number disappears. I reenter the number but it disappears again the following month. Is this a known issue? Is there a fix? ...

Invoice lookup by paid check number
I frequently have vendors call me asking for information about what invoices were paid by a check number. Is ther an easy way to look this information up? -- Rodger You could go to Inquiry>Purchasing>Transactions by Document, put your check number in the 'from' and 'to' fields. Once the document is displayed in the scrolling window zoom back on the 'Unapplied Amount' field. Viola! the documents paid by the selected payment are listed. Unfortunately there isn't a print icon on this inquiry window, but I think it's the information you wanted. &quo...

Lookup
Q103 Q102 Q202 Q302 Q402 Q103 Q203 How can I lookup the Q103 in the row above and then have it pull the number to the right one cell (Q203)? thanks If I understand correctly =INDEX(A2:F2,MATCH(A1,A2:F2,0)+1) where q103 is in a1 and q102-q203 is in a2-f2 Lance >-----Original Message----- >Q103 > > >Q102 Q202 Q302 Q402 Q103 Q203 > > >How can I lookup the Q103 in the row above and then have >it pull the number to the right one cell (Q203)? thanks >. > matt wrote: > Q103 > > > Q102 Q202 Q302 Q402 Q...

Loan numbering stops
Loan numbering has stopped when I last changed my payment amount due to escrow recacluation. I get this error attempting to manually assign them: "You cannot change this transaction into an electronic payment or trnasfer because the date is not within an acceptable range and cannot be changed" How do I get the numbering restored? ...

A Lookup function does not work
Hi, This is my first posting. I am using Exel 2000. I have 2 separate spreadsheets that have some similar columns but not all of the data in the similar columns is the same. What I want to do is take column A in spreadsheet#1 and find this same value in Column B in Spreadsheet#2 and then insert into column 3 in spreadsheet #1 a value from a different column in spreasheet #2 that corresponds to the row in which the value was looked up in Column B in spreadsheet#2. What I am doing is comparing 2 different inventory files that have stock codes in columns and quantities in another column, but n...

(?) Entering negative number in budget planner?
In Money 2007 Deluxe , I am using the Savings and Spending Budget tool. How do I enter a negative number in the planned amount for a category? For instance, in March I have to pay my property tax, so for the planned amount for the Net Contributions to Escrow Savings Account, I want to enter -$1070.00 to plan for paying the property tax from this account. Or when my employer reimburses me for some home office expenses, I want to enter the reimbursement as a negative number for that expense category. Money will calculate a negative number based on existing transactions, but I can't f...

how to stop repeating a number in worksheet(e.g. cell A1 is 6655 .
I want to to know I can stop a number to repeat in excel worksheet. (e.g. cell A1 is 6655 so 6655 should not repeat inwhere in the worksheet of even if it appears there could be a pop up massage says number repeated in cell# A1) You can use data validation to prevent duplicates. There are instructions here: http://www.contextures.com/xlDataVal07.html And to include a message, add an error alert or input message: http://www.contextures.com/xlDataVal04.html Mushtaque Parker wrote: > I want to to know I can stop a number to repeat in excel worksheet. (e.g. > cell A1 is 6655 s...

Help with LOOKUP function
This function is in a workbook with 2 sheets. It _almost_ works perfectly. These "C" columns in two different sheets '2005-2006'!C:C,'2004-2005'!C:C, contain names of people. The D column in one of the sheets - '2004-2005'!D:D - contains a date associated with the person's name from the C column of 2004-2005 sheet. This formula is in the "D" column of Sheet 2005-2006. =LOOKUP('2005-2006'!C:C,'2004-2005'!C:C,'2004-2005'!D:D) The concept is for the formula to lookup the value (person's name) in column C of 2005-2006 a...

return cell reference in a table based upon given lookup criteria
Is there a way to return the cell reference, or column/row coordinates, of a cell within an array or table by providing lookup criteria? Perhaps something like this: For a table of value in A1:E10 F1: (the value to find) G1: =ADDRESS(MAX((A1:E10=F1)*ROW(A1:E10)),MAX((A1:E10=F1)*COLUMN(A1:E10))) Note: Commit that array formula by holding down the [Ctrl][Shift] keys and press [Enter]. That formula returns the address of the 1st cell containing the value in F1, or #VALUE! if there is no match. Am I on the right track here? *********** Regards, Ron XL2002, WinXP-Pro "Travis" ...

Why won't page numbers print on hard copy in publisher?
I have a publication ready to go to print in Microsoft Publisher but when I print pages from it the page numbers are not there. Be certain you don't have "ignore Master Page (background)" checked in the view menu. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "RPMWINDSOR" <RPMWINDSOR@discussions.microsoft.com> wrote in message news:584E8FA6-2E47-4D5B-A956-17603D697005@microsoft.com... >I have a publication ready to go to print in Microsoft Publisher but when I > print pages from it the page numbe...

Advanced Lookups
Is there any way to make an advanced lookup the default lookup? so you don't have to always choose that option when doing a lookup? Thanks for any help. Tracey D Advanced lookups ARE the default unless you've done something to make it now so. There isn't any way to "choose" the option when doing a lookup that I know of unless you have some type of customization (easy to do) that would give the user an option. patrick dev support -- This posting is provided "AS IS" with no warranties, and confers no rights. "Tracey D" <...

Calling employee lookup from button through VBA code
Dear All, Can anyone show me how to call an existing GP employee lookup from a button of a modified form through VBA code. Thanks in advance. -- Developer Hi, If I'm understanding the question - you need to add the lookup button to your project and make sure your project provides that it runs on the modified form. Leslie "Dexdev" wrote: > Dear All, > > Can anyone show me how to call an existing GP employee lookup from a button > of a modified form through VBA code. > > > Thanks in advance. > > -- > Developer Hello Dexdev As per...

Vendor Lookup
One doing the vendor lookup - one user sees the 'show details' information upon lookup; other user sees the vendor list and needs to clik on the show details - how do you get the show details window to be the default option you see. Thansk! Check for full stops/periods/dots on the window title bar before or after the window name. It is possible to use VBA or modifier to open the details automatically. David Musgrave [MSFT] Escalation Engineer - Microsoft Dynamics GP Microsoft Dynamics Support - Asia Pacific Microsoft Dynamics (formerly Microsoft Business Solutions) http://www...

Number of hrs worked in a week, based on dates in a different column
Hi. I'm hoping someone will give this a try. I'd like to enter a function into each cell in column D so that the total hours worked for the previous week appears each Sunday. For instance, cell D6 would display 11, the sum of C2:C6 (note this is a short week). Cell D20 would display 22.5, the sum of C7:C20. On some days, no work at all is performed, but on other days multiple tasks are performed. The only cells in column D that should display a value are the Sunday's... all other cells should display nothing. Thanks very much for your help!! Gregg. ...

max number of fields on a form (new question)
I am creating a database based on a paper form. The paper form has over 360 individual pieces of information, all of which are recorded for research purposes. My boss would like me to create a replica electronic form based on the paper form, but access only allows 255 fields on one form. Other than subforms, is there any other way to get around this? I have my data organized into related tables, but every field from each table needs to be on the form. Any suggestions? The limit of 255 is not the form. Forms have a lifetime limit of 754 controls. That means that if you delete a control the...

Numbers converting to decimal
I a trying to figure out why when I type 11 and automatically converts it to .11, if I type 11. it will stay 11,if I change all the cells to text then back to number they willstay. I have checked the formatting of the cells, it even happens when I open a brand new worksheet. Any ideas? Thanks Dawn Hi Dawn, Tools>Option>Edit, uncheck Fixed Decimal -- Kind Regards, Niek Otten Microsoft MVP - Excel "DawnP" <anonymous@discussions.microsoft.com> wrote in message news:c3cf01c48a05$d75359d0$a501280a@phx.gbl... > I a trying to figure out why when I type 11 and &...

numbers
I've got a huge sheet of data that I'm trying to change into a specific format. I'm putting formulas on sheet 1 referencing the data on sheet 2 but putting it into the format I need. The only problem is with the numbers. On the data sheet I have weights and measurements in this format: 0.1500 8.5000 3.0000 2.2500 0.2500 7.2500 2.2500 2.0000 0.1000 8.5000 3.0000 1.7500 That needs to be on the other sheet in this format: 00001500 00085000 00030000 00022500 00002500 00072500 00022500 00020000 00001000 00085000 00030000 00017500 Ignoring the extra spacing between, how can I get ...

Number field question
In a table called Payments; I have a field called CheckNumber. Its data type is number Its field size is Long Integer Its format is General Number Whenever the check number is more than 4 digits I get "1E+0" printed on the receipt. What am I overlooking? Thanks for any help. -- Posted via a free Usenet account from http://www.teranews.com Try... Report? Set the field so it can grow. Datasheet view? Widen the field Form? Widen the field Query? widen the field If this database is to used by others AND you are entering check numbers via the table you might want to consider...

Lookup #4
I am trying to label my bank info that I import into Excel. I have a column with the charge amount and a column with the recipient. I think a lookup table would work for this, but I need to have it check for wildcards. For example, the grocery chain is numbered here, so one might be GROCER 5454 and one may be GROCER 8724, so I need the function to look for GROCER*, right? Also, if it does not fit any category, how can I get it to use "Other"? Do I make that one just "*"? Mine is not working very well. Maybe my syntax is wrong. Any ideas? TIA Is there anyway you can st...

Filter message by reverse domain lookup
Hello all, I am kinda new to Exchange and was wondering if there was a function built into Exchange 2000 that would "look" at the source domain of the email message and then perform a "reverse" DNS lookup to verify that the domain actually exists and if it doesn't then have Exchange discard the email without sending an NDR? Is this possible strait out of the box? It seems like I remember iMail gateway having this feature. I am trying to filter out some of the spam that fills up my users inboxes. Thanks in advance. Jeremy jsteger@bellsouth.net (Jeremy Steger) wrote...

VBA/Row number/column numer
If I want to specify a cell in VBA but only know it's row NUMBer and not column number, how would I describe this cell in vba? i.e. instead of AA5, i want to be able to talkb about it as being column 27 and row 5... thanks! Mika, Cells(Row, Column) Cells(2, 3) would give you cell C2 -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "mika." <conceptualness@hotmail.com> wrote in message news:07ad01c37be7$ffd1b0a0$a101280a@phx.gbl... > If I want to specify a cell in VBA but only know it's row > NUMBer a...

Excel: How can I get numbers without decimals ?
After entering numbers in a cell they appear as decimals. I have tried going into Format and changing it to 0 decimal but then I get 0. Can you please help me. Tools>Options>Edit, uncheck Fixed decimal -- Kind Regards, Niek Otten Microsoft MVP - Excel "Wamalapada" <Wamalapada@discussions.microsoft.com> wrote in message news:DD30288E-CBD7-4CA8-B59F-896D37216F02@microsoft.com... > After entering numbers in a cell they appear as decimals. I have tried > going > into Format and changing it to 0 decimal but then I get 0. > > Can you please help me. ...

Can you change the default lookup from Account to Contact?
Is it possible to set the lookup for a customer field to default on "contacts" rather then "accounts?" We do more business with contacts so it is a pain to have to change this all the time. there is no supported way to do this in the current release -- John O'Donnell Microsoft CRM MVP http://www.microsoft.com/BusinessSolutions/Community/CRMFaqLanding.aspx "Martin Flaherty" <mflaherty@techpg.com> wrote in message news:ucdNM74uEHA.3416@TK2MSFTNGP09.phx.gbl... > Is it possible to set the lookup for a customer field to default on > "contac...

Formula to lookup named reference based on value
I cant' figure out what the exact words that I'm looking for and if I did I would most likely find the answer. I've got a spreadsheet that has a series of numbers in say column A and it has the numbers 1,2,3,4,5 Further on in the spreadsheet I have named values like CA.1, CA.2, CA. 3 What I want to do is in the formula go: =(X1/SUM(X:X,X,X:X)*CA.2) (for the named range CA.2) Is it possible to have something that goes.... =(X1/SUM(X:X,X,X:X)*"CA"&B4) where B4 = 2 Thus the outcome would be CA.2 ????? =(X1/SUM(X:X,X,X:X)*INDIRECT("CA"&B4)) -- Kind...