Excel help
I have a report I need to make which has turned out to be quite complicated. i need to use some form of lookup function to find info from one worksheet and place it in another, but the names I need to reference are slightly different on both sheets as they have come from different sources and have different abbreviations (hope this makes sense so far!). When i use an exact lookup it returns N/A, and when i use a 'best result' lookup it returns the closest, but usually wrong answer. Is there a way of looking up the first few letters in a string of text? Any ideas appreciated! -- chazpot ------------------------------------------------------------------------ chazpot's Profile: http://www.excelforum.com/member.php...o&userid=26353 View this thread: http://www.excelforum.com/showthread...hreadid=396178 |
chazpot wrote...
I have a report I need to make which has turned out to be quite complicated. i need to use some form of lookup function to find info from one worksheet and place it in another, but the names I need to reference are slightly different on both sheets as they have come from different sources and have different abbreviations (hope this makes sense so far!). When i use an exact lookup it returns N/A, and when i use a 'best result' lookup it returns the closest, but usually wrong answer. Is there a way of looking up the first few letters in a string of text? 'Best result' lookup? Excel has poor text processing tools, and approximate text matching is a text processing task rather than a calculation task. There's probably no way to automate this reliably de novo, BUT you could begin to maintain a cross-reference table of names from both worksheets so you wouldn't need to match names manually more than once (but no way to avoid manual matching once). Perhaps use a third worksheet to hold names from one worksheet in column A and the matching names from the other worksheet in column B. Better to put the names from the report exhibit worksheet in column A. Then you could use formulas like ReportExhibit!B5: (assuming the cross-referenced name in A5) =SUMIF(ReportData!$A$3:$A$9999,VLOOKUP(A5,CrossRef erence!$A$1:$B$20000,2), ReportData!$Z$3:$Z$9999) |
All times are GMT +1. The time now is 01:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com