Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP Help
I have a spreadsheet with over 12000 enties in it on sheet 1 named Details.
There are 3 columns with data, col A PART NUMBER, col B PART DISCRIPTION and col C PRICE. I have added in a second sheet that I wish to use that if I put in the Part number in col A it will return the price in col B. In col B i have used the following =VLOOKUP(A1,Details!A1:C12668,3,FALSE) Using this formula whatever part number I enter in A1 I receive N/A in B1 even though that pert number exists. If I change the formula to =VLOOKUP(A2,Details!A1:C12668,3,TRUE) I receive a price but it is incorrect. I have sorted sheet 1 Details into ascending order. Please help me with this. Many Thanks Dave |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP Help
Well the thing i guess is that it may be the format of the partnumber in the
detail sheet is deffrent to the partnumber that you enter in the summary sheet so if you just want to find one by one why you don't use Find feature that Excel has? Thanks, -- Farhad Hodjat "Racingzeus" wrote: I have a spreadsheet with over 12000 enties in it on sheet 1 named Details. There are 3 columns with data, col A PART NUMBER, col B PART DISCRIPTION and col C PRICE. I have added in a second sheet that I wish to use that if I put in the Part number in col A it will return the price in col B. In col B i have used the following =VLOOKUP(A1,Details!A1:C12668,3,FALSE) Using this formula whatever part number I enter in A1 I receive N/A in B1 even though that pert number exists. If I change the formula to =VLOOKUP(A2,Details!A1:C12668,3,TRUE) I receive a price but it is incorrect. I have sorted sheet 1 Details into ascending order. Please help me with this. Many Thanks Dave |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP Help
Hi, The format for Part Number on both sheets are set to number and on the
summary sheet I am entering lots of part numbers and then auto sum to give me an incoice total so the find feature would not be of any use. Dave "Farhad" wrote: Well the thing i guess is that it may be the format of the partnumber in the detail sheet is deffrent to the partnumber that you enter in the summary sheet so if you just want to find one by one why you don't use Find feature that Excel has? Thanks, -- Farhad Hodjat "Racingzeus" wrote: I have a spreadsheet with over 12000 enties in it on sheet 1 named Details. There are 3 columns with data, col A PART NUMBER, col B PART DISCRIPTION and col C PRICE. I have added in a second sheet that I wish to use that if I put in the Part number in col A it will return the price in col B. In col B i have used the following =VLOOKUP(A1,Details!A1:C12668,3,FALSE) Using this formula whatever part number I enter in A1 I receive N/A in B1 even though that pert number exists. If I change the formula to =VLOOKUP(A2,Details!A1:C12668,3,TRUE) I receive a price but it is incorrect. I have sorted sheet 1 Details into ascending order. Please help me with this. Many Thanks Dave |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP Help
Hi Dave,
It is possible the part numbers look alike but one may have a leading or trailing space. In an empty cell enter =A1=Details!A1 to test for True or False. Where the A1 cells of each sheet are a pair of troublesome lookups. You will get a True or False, and I suspect yours will return False. If so adjust as needed to clean up the part numbers of the troubled sheet. Also, you may want to try this with 'clean' part numbers on both sheets. Select two cells in a row where you want your lookup values to appear, (the description and the price). While still selected type in this formula: =VLOOKUP(D1,A1:C5,{2,3},0) Now hit Ctrl + Shift + Enter, you should get { } brackets around the formulas and you description will be in first cell and the price in the second. Of course adjust the cell address's to suit your sheet. And your list does not need to be sorted when using the FALSE or 0 as the fourth argument. HTH Regards, Howard "Racingzeus" wrote in message ... I have a spreadsheet with over 12000 enties in it on sheet 1 named Details. There are 3 columns with data, col A PART NUMBER, col B PART DISCRIPTION and col C PRICE. I have added in a second sheet that I wish to use that if I put in the Part number in col A it will return the price in col B. In col B i have used the following =VLOOKUP(A1,Details!A1:C12668,3,FALSE) Using this formula whatever part number I enter in A1 I receive N/A in B1 even though that pert number exists. If I change the formula to =VLOOKUP(A2,Details!A1:C12668,3,TRUE) I receive a price but it is incorrect. I have sorted sheet 1 Details into ascending order. Please help me with this. Many Thanks Dave |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP Help
Hi Howard
When I enter =A1=Details!A1 I result with False. I have looked at the part number and dont see any leading or trailing spaces etc. I dont fully understand what you mean by "Where the A1 cells of each sheet are a pair of troublesome lookups. You will get a True or False, and I suspect yours will return False. If so adjust as needed to clean up the part numbers of the troubled sheet." I am new to working with Excel formulas so please excuse my lack of knowledge in this:) What exactly does =A1=Details!A1 do and why does it return False ? Also if there are any spaces or unseen characters in my part numbers is there any way to remove these as to edit over 12000 lines would be a task :( as the part numbers where imported into Excel from an Access Database. Regards Dave "L. Howard Kittle" wrote: Hi Dave, It is possible the part numbers look alike but one may have a leading or trailing space. In an empty cell enter =A1=Details!A1 to test for True or False. Where the A1 cells of each sheet are a pair of troublesome lookups. You will get a True or False, and I suspect yours will return False. If so adjust as needed to clean up the part numbers of the troubled sheet. Also, you may want to try this with 'clean' part numbers on both sheets. Select two cells in a row where you want your lookup values to appear, (the description and the price). While still selected type in this formula: =VLOOKUP(D1,A1:C5,{2,3},0) Now hit Ctrl + Shift + Enter, you should get { } brackets around the formulas and you description will be in first cell and the price in the second. Of course adjust the cell address's to suit your sheet. And your list does not need to be sorted when using the FALSE or 0 as the fourth argument. HTH Regards, Howard "Racingzeus" wrote in message ... I have a spreadsheet with over 12000 enties in it on sheet 1 named Details. There are 3 columns with data, col A PART NUMBER, col B PART DISCRIPTION and col C PRICE. I have added in a second sheet that I wish to use that if I put in the Part number in col A it will return the price in col B. In col B i have used the following =VLOOKUP(A1,Details!A1:C12668,3,FALSE) Using this formula whatever part number I enter in A1 I receive N/A in B1 even though that pert number exists. If I change the formula to =VLOOKUP(A2,Details!A1:C12668,3,TRUE) I receive a price but it is incorrect. I have sorted sheet 1 Details into ascending order. Please help me with this. Many Thanks Dave |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP Help
Howard,
I have took another look at the Part Numbers and there is a trailing space at the end of every number. Is there any way I can remove thes ? Dave "Racingzeus" wrote: Hi, The format for Part Number on both sheets are set to number and on the summary sheet I am entering lots of part numbers and then auto sum to give me an incoice total so the find feature would not be of any use. Dave "Farhad" wrote: Well the thing i guess is that it may be the format of the partnumber in the detail sheet is deffrent to the partnumber that you enter in the summary sheet so if you just want to find one by one why you don't use Find feature that Excel has? Thanks, -- Farhad Hodjat "Racingzeus" wrote: I have a spreadsheet with over 12000 enties in it on sheet 1 named Details. There are 3 columns with data, col A PART NUMBER, col B PART DISCRIPTION and col C PRICE. I have added in a second sheet that I wish to use that if I put in the Part number in col A it will return the price in col B. In col B i have used the following =VLOOKUP(A1,Details!A1:C12668,3,FALSE) Using this formula whatever part number I enter in A1 I receive N/A in B1 even though that pert number exists. If I change the formula to =VLOOKUP(A2,Details!A1:C12668,3,TRUE) I receive a price but it is incorrect. I have sorted sheet 1 Details into ascending order. Please help me with this. Many Thanks Dave |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP Help
I'll get right back with you in about 1 hour. Need to go
Regards, Howard "Racingzeus" wrote in message ... I have a spreadsheet with over 12000 enties in it on sheet 1 named Details. There are 3 columns with data, col A PART NUMBER, col B PART DISCRIPTION and col C PRICE. I have added in a second sheet that I wish to use that if I put in the Part number in col A it will return the price in col B. In col B i have used the following =VLOOKUP(A1,Details!A1:C12668,3,FALSE) Using this formula whatever part number I enter in A1 I receive N/A in B1 even though that pert number exists. If I change the formula to =VLOOKUP(A2,Details!A1:C12668,3,TRUE) I receive a price but it is incorrect. I have sorted sheet 1 Details into ascending order. Please help me with this. Many Thanks Dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
IF(a1="x",(vlookup 18K rows,2,false),(vlookup 18K,3,false)) RAM? | Excel Worksheet Functions | |||
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |