Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with .csv data
i pull a csv file from a website with 3 columns of data approx. 450 rows
long. i.e. Col. 1 Col.2 Col. 3 ABC ABC Co. 1.02 BCD BCD Inc. 0.89 CDE CDE Assoc. 1.45 i copy and paste this data into an existing file on Sheet 2. The last column (F) on Sheet 1 has text that corresponds with column 1 above. I am trying to use VLOOKUP in Sheet 1 column (G) to bring the Col. 3 data to (G). When i use the VLOOKUP it brings back the wrong data. It returns the data from the correct column, but the wrong row. It returns the row ABOVE the correct response. =VLOOKUP(CDE,Sheet2!A1:E57,3) returns 0.89 (instead of 1.45) =VLOOKUP(CDE,Sheet2!A1:E57,3,True) returns 0.89 =VLOOKUP(CDE,Sheet2!A1:E57,3,False) returns #N/A the only way i have been able to make this work is to retype the data in Col.1. but i need to be able to do this on demand and since this data changes and there are 450+ rows, retyping is not practical. Help PLEASE. thanks -- ty |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with .csv data
See example VLOOKUP with MATCH function in 'Peter Noneley's function dictionary' (http://www.rondebruin.nl/files/xlfdic01.zip) I believe this could solve your problem. Regards Atmaram -- pvanrooijen ------------------------------------------------------------------------ pvanrooijen's Profile: http://www.excelforum.com/member.php...o&userid=26090 View this thread: http://www.excelforum.com/showthread...hreadid=565750 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with .csv data
Vlookup has an optional final argument that I suspect you are not providing.
If this argument is TRUE or omitted, Vlookup will return an approximate match - the largest value it finds which is smaller than your target. If you specify FALSE for the final argument, an exact match is required. See the help on Vlookup. Hope this helps, Hutch "ty" wrote: i pull a csv file from a website with 3 columns of data approx. 450 rows long. i.e. Col. 1 Col.2 Col. 3 ABC ABC Co. 1.02 BCD BCD Inc. 0.89 CDE CDE Assoc. 1.45 i copy and paste this data into an existing file on Sheet 2. The last column (F) on Sheet 1 has text that corresponds with column 1 above. I am trying to use VLOOKUP in Sheet 1 column (G) to bring the Col. 3 data to (G). When i use the VLOOKUP it brings back the wrong data. It returns the data from the correct column, but the wrong row. It returns the row ABOVE the correct response. =VLOOKUP(CDE,Sheet2!A1:E57,3) returns 0.89 (instead of 1.45) =VLOOKUP(CDE,Sheet2!A1:E57,3,True) returns 0.89 =VLOOKUP(CDE,Sheet2!A1:E57,3,False) returns #N/A the only way i have been able to make this work is to retype the data in Col.1. but i need to be able to do this on demand and since this data changes and there are 450+ rows, retyping is not practical. Help PLEASE. thanks -- ty |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with .csv data
Try this =VLOOKUP(CDE,Sheet2!$A$1:$E$57,3,False) The $ sign makes the formula absolute reference, therefore when you drag it down the column the table reference stay the same. Other wise your formula will progressively get out of range A1:E57 will change to A2:E58 A3:E59 If you are using the Vlookup wizard after you enter the table range hit F4 to make the absolut reference other wise just add the dollar signs. Hope this work. Lostinformulas -- lostinformulas ------------------------------------------------------------------------ lostinformulas's Profile: http://www.excelforum.com/member.php...o&userid=35229 View this thread: http://www.excelforum.com/showthread...hreadid=565750 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup to Return a Range of Data | Excel Discussion (Misc queries) | |||
.csv file Multiple columns of data in single Cell | Excel Discussion (Misc queries) | |||
ranking query | Excel Discussion (Misc queries) | |||
Vlookup for data contained in a cell | Excel Worksheet Functions | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |