Precisely the reason why I responded with the core array expression to
retrieve it "as-is" from Sheet2's col D, separate from ensuing action to
strip out what-have-you.

There could be various reasons why the #N/A:
a. Did you correctly array-enter it, before propagating across/down? Visual
check by looking in the formula bar, there should be curly braces: {..}
wrapped by Excel, post confirmation. If there's none, then it isn't
array-entered, and you have to confirm it again (click inside the formula
bar, do the CSE), and re-check for the curlies.

b. If (a) is ok, then there's data inconsistency preventing what should
otherwise have matched correctly, eg extra white spaces. You could use TRIM
for increased robustness in the matching of the concat lookups vs the array
in Sheet2's col C, eg:
=INDEX(Sheet2!$D$2:$D$100,MATCH(MAX(IF(TRIM(Sheet2 !$C$2:$C$100)=TRIM(B$1&$A2),Sheet2!$B$2:$B$100)),I F(TRIM(Sheet2!$C$2:$C$100)=TRIM(B$1&$A2),Sheet2!$B $2:$B$100),0))

Get the above returning correctly first before you proceed to tackle the 2nd
part. You could start a new thread for the 2nd part, elaborating on the full
range of possible values in Sheet2's col D, and what you want stripped out.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"lawandgrace" wrote:
Sorry, I've tried several times and I keep getting the #N/A error code. Also,
I do not have to search for the dash as not all the returned data will
necessarily have a dash (it might return a single number), so for that
instance I think I just need something like =RIGHT(cell,1). But I could be
wrong :-)