Excel help

[chazpot]

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!

 

[Harlan Grove]

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,CrossReference!$A$1:$B$20000,2),
ReportData!$Z$3:$Z$9999)