Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
spread sheets vlookup
I need some help with vlook up.. I cannot seem to get it right. i have to
make it to where when i put a number in it also puts in the description and unit price just by the number i put in to a cell.. can anyone please help me.. I am making an invoice... and using a look up table to do the vlookup function but dont know what i am doing wrong someone please help me asap... thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
spread sheets vlookup
Check out this web page of Debra Dalgleish that should help you:
http://www.contextures.com/xlFunctions02.html -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "joyce" wrote in message ... I need some help with vlook up.. I cannot seem to get it right. i have to make it to where when i put a number in it also puts in the description and unit price just by the number i put in to a cell.. can anyone please help me.. I am making an invoice... and using a look up table to do the vlookup function but dont know what i am doing wrong someone please help me asap... thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
spread sheets vlookup
I am actualy on that sitte now.. and i still dont see what i am doing wrong.
"Ragdyer" wrote: Check out this web page of Debra Dalgleish that should help you: http://www.contextures.com/xlFunctions02.html -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "joyce" wrote in message ... I need some help with vlook up.. I cannot seem to get it right. i have to make it to where when i put a number in it also puts in the description and unit price just by the number i put in to a cell.. can anyone please help me.. I am making an invoice... and using a look up table to do the vlookup function but dont know what i am doing wrong someone please help me asap... thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
spread sheets vlookup
Try explaining what you want to do and include what formulas you have tried,
where the data is and what type of data it is. The more details you provide the better chance you have of getting help that solves the problem. Details.........Details..........Details!!! Biff "joyce" wrote in message ... I am actualy on that sitte now.. and i still dont see what i am doing wrong. "Ragdyer" wrote: Check out this web page of Debra Dalgleish that should help you: http://www.contextures.com/xlFunctions02.html -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "joyce" wrote in message ... I need some help with vlook up.. I cannot seem to get it right. i have to make it to where when i put a number in it also puts in the description and unit price just by the number i put in to a cell.. can anyone please help me.. I am making an invoice... and using a look up table to do the vlookup function but dont know what i am doing wrong someone please help me asap... thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
spread sheets vlookup
Show us your VLOOKUP formulas and the layout of your lookup table.
To get price and description you need two VLOOKUP formulas. The table should be at least 3 columns.........number, price, description. Gord Dibben MS Excel MVP On Sun, 30 Apr 2006 19:45:02 -0700, joyce wrote: I am actualy on that sitte now.. and i still dont see what i am doing wrong. "Ragdyer" wrote: Check out this web page of Debra Dalgleish that should help you: http://www.contextures.com/xlFunctions02.html -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "joyce" wrote in message ... I need some help with vlook up.. I cannot seem to get it right. i have to make it to where when i put a number in it also puts in the description and unit price just by the number i put in to a cell.. can anyone please help me.. I am making an invoice... and using a look up table to do the vlookup function but dont know what i am doing wrong someone please help me asap... thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
spread sheets vlookup
I am doing an invoice but put it this way, cell A6 is validated with only certain #'s cell b6 is description cell c6 is quantity cell d6 isunit price cell e6 is extended price now using vlookup for b6 and d6. okay now it returns a #N/A, but we are suppose to have it a blank cell so i used =IF(ISNA(VLOOKUP(A14,products,2)),"",VLOOKUP(A14,p roducts,2)), okay this made the #n/a go blank in the cell perfect... but in cell e6 now it says #value...I think it is suppose to be blank as well. how can i make it blank it also has a formula in it which is =c6*d6, but when i chose cell a6 and put in the number i am suppose to everything is correct there. what do i do about the #value or is it suppose to be there until the info is put into cell a6??????????????????? Please help me as soon as possible... thanks joyce oh i am using excel 2003 "Gord Dibben" wrote: Show us your VLOOKUP formulas and the layout of your lookup table. To get price and description you need two VLOOKUP formulas. The table should be at least 3 columns.........number, price, description. Gord Dibben MS Excel MVP On Sun, 30 Apr 2006 19:45:02 -0700, joyce wrote: I am actualy on that sitte now.. and i still dont see what i am doing wrong. "Ragdyer" wrote: Check out this web page of Debra Dalgleish that should help you: http://www.contextures.com/xlFunctions02.html -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "joyce" wrote in message ... I need some help with vlook up.. I cannot seem to get it right. i have to make it to where when i put a number in it also puts in the description and unit price just by the number i put in to a cell.. can anyone please help me.. I am making an invoice... and using a look up table to do the vlookup function but dont know what i am doing wrong someone please help me asap... thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
spread sheets vlookup
Since you're matching on part numbers, I would think you'd want a formula like:
=if(isna(vlookup(a4,products,2,false)),"",vlookup( a4,products,2,false)) That 4th argument (False) forces an exact match. Then to get the extended price, you could use: =if(d4="","",d4*c4) or =if(d4="",0,d4*c4) (if you want to show the extended price as 0. joyce wrote: I am doing an invoice but put it this way, cell A6 is validated with only certain #'s cell b6 is description cell c6 is quantity cell d6 isunit price cell e6 is extended price now using vlookup for b6 and d6. okay now it returns a #N/A, but we are suppose to have it a blank cell so i used =IF(ISNA(VLOOKUP(A14,products,2)),"",VLOOKUP(A14,p roducts,2)), okay this made the #n/a go blank in the cell perfect... but in cell e6 now it says #value...I think it is suppose to be blank as well. how can i make it blank it also has a formula in it which is =c6*d6, but when i chose cell a6 and put in the number i am suppose to everything is correct there. what do i do about the #value or is it suppose to be there until the info is put into cell a6??????????????????? Please help me as soon as possible... thanks joyce oh i am using excel 2003 "Gord Dibben" wrote: Show us your VLOOKUP formulas and the layout of your lookup table. To get price and description you need two VLOOKUP formulas. The table should be at least 3 columns.........number, price, description. Gord Dibben MS Excel MVP On Sun, 30 Apr 2006 19:45:02 -0700, joyce wrote: I am actualy on that sitte now.. and i still dont see what i am doing wrong. "Ragdyer" wrote: Check out this web page of Debra Dalgleish that should help you: http://www.contextures.com/xlFunctions02.html -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "joyce" wrote in message ... I need some help with vlook up.. I cannot seem to get it right. i have to make it to where when i put a number in it also puts in the description and unit price just by the number i put in to a cell.. can anyone please help me.. I am making an invoice... and using a look up table to do the vlookup function but dont know what i am doing wrong someone please help me asap... thanks -- Dave Peterson |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
spread sheets vlookup
Hi Dave
thanks that worked, I dont know why i didnt think of that one... maybe i was thinking that those cells werent suppose to have an if statement.. But it looks good and still get the correct answers.. I soooooooooo appreciate it... the first if statement you gave, does it really make a difference if you put false in it??? cause without it , it comes up with the same thing. Thanks again.. Joyce "Dave Peterson" wrote: Since you're matching on part numbers, I would think you'd want a formula like: =if(isna(vlookup(a4,products,2,false)),"",vlookup( a4,products,2,false)) That 4th argument (False) forces an exact match. Then to get the extended price, you could use: =if(d4="","",d4*c4) or =if(d4="",0,d4*c4) (if you want to show the extended price as 0. joyce wrote: I am doing an invoice but put it this way, cell A6 is validated with only certain #'s cell b6 is description cell c6 is quantity cell d6 isunit price cell e6 is extended price now using vlookup for b6 and d6. okay now it returns a #N/A, but we are suppose to have it a blank cell so i used =IF(ISNA(VLOOKUP(A14,products,2)),"",VLOOKUP(A14,p roducts,2)), okay this made the #n/a go blank in the cell perfect... but in cell e6 now it says #value...I think it is suppose to be blank as well. how can i make it blank it also has a formula in it which is =c6*d6, but when i chose cell a6 and put in the number i am suppose to everything is correct there. what do i do about the #value or is it suppose to be there until the info is put into cell a6??????????????????? Please help me as soon as possible... thanks joyce oh i am using excel 2003 "Gord Dibben" wrote: Show us your VLOOKUP formulas and the layout of your lookup table. To get price and description you need two VLOOKUP formulas. The table should be at least 3 columns.........number, price, description. Gord Dibben MS Excel MVP On Sun, 30 Apr 2006 19:45:02 -0700, joyce wrote: I am actualy on that sitte now.. and i still dont see what i am doing wrong. "Ragdyer" wrote: Check out this web page of Debra Dalgleish that should help you: http://www.contextures.com/xlFunctions02.html -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "joyce" wrote in message ... I need some help with vlook up.. I cannot seem to get it right. i have to make it to where when i put a number in it also puts in the description and unit price just by the number i put in to a cell.. can anyone please help me.. I am making an invoice... and using a look up table to do the vlookup function but dont know what i am doing wrong someone please help me asap... thanks -- Dave Peterson |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
spread sheets vlookup
If I want an exact match, I'd add False.
I think if you test some bad part numbers or sort your table differently, you'll see the problem. Do your tests against a copy of the original workbook. joyce wrote: Hi Dave thanks that worked, I dont know why i didnt think of that one... maybe i was thinking that those cells werent suppose to have an if statement.. But it looks good and still get the correct answers.. I soooooooooo appreciate it... the first if statement you gave, does it really make a difference if you put false in it??? cause without it , it comes up with the same thing. Thanks again.. Joyce "Dave Peterson" wrote: Since you're matching on part numbers, I would think you'd want a formula like: =if(isna(vlookup(a4,products,2,false)),"",vlookup( a4,products,2,false)) That 4th argument (False) forces an exact match. Then to get the extended price, you could use: =if(d4="","",d4*c4) or =if(d4="",0,d4*c4) (if you want to show the extended price as 0. joyce wrote: I am doing an invoice but put it this way, cell A6 is validated with only certain #'s cell b6 is description cell c6 is quantity cell d6 isunit price cell e6 is extended price now using vlookup for b6 and d6. okay now it returns a #N/A, but we are suppose to have it a blank cell so i used =IF(ISNA(VLOOKUP(A14,products,2)),"",VLOOKUP(A14,p roducts,2)), okay this made the #n/a go blank in the cell perfect... but in cell e6 now it says #value...I think it is suppose to be blank as well. how can i make it blank it also has a formula in it which is =c6*d6, but when i chose cell a6 and put in the number i am suppose to everything is correct there. what do i do about the #value or is it suppose to be there until the info is put into cell a6??????????????????? Please help me as soon as possible... thanks joyce oh i am using excel 2003 "Gord Dibben" wrote: Show us your VLOOKUP formulas and the layout of your lookup table. To get price and description you need two VLOOKUP formulas. The table should be at least 3 columns.........number, price, description. Gord Dibben MS Excel MVP On Sun, 30 Apr 2006 19:45:02 -0700, joyce wrote: I am actualy on that sitte now.. and i still dont see what i am doing wrong. "Ragdyer" wrote: Check out this web page of Debra Dalgleish that should help you: http://www.contextures.com/xlFunctions02.html -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "joyce" wrote in message ... I need some help with vlook up.. I cannot seem to get it right. i have to make it to where when i put a number in it also puts in the description and unit price just by the number i put in to a cell.. can anyone please help me.. I am making an invoice... and using a look up table to do the vlookup function but dont know what i am doing wrong someone please help me asap... thanks -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP on multiple sheets | Excel Discussion (Misc queries) | |||
Spread sheets for keeping records of Art prints, printing & sales | New Users to Excel | |||
In 3 active sheets in wkbk, determine& display the # of sheets that have data | Excel Discussion (Misc queries) | |||
exel spread sheets | Excel Worksheet Functions | |||
how do I get a download for excel spread sheets only | Excel Discussion (Misc queries) |