Unusual Match / lookup function

[df9864]

Hi - would appreciate help with this one. I want to use a standard Excel function to solve a lookup based on criteria problem...

I have a 'key' on worksheet(1) with (let's say) 2 columns A & B - example values as follows (approx 75 in total).
GIAA 50%
GIAB 100%
GYEFF 80%
etc
where column A reflects a designated summary point in a hierarchy built by adding progressive characters to indicate parent-child relationships - eg. G has two children: GA & GB which in turn have childen GAA, GAB, GAC & GBA, GBB, GBC - you get the idea !! Note they are NOT all at the same level - ie. they can have different char lengths.
The second column (mygroup1, etc) denotes a % that I need to carry to the data sheet.

The Data on worksheet(2) has a column of alpha values that are at the lowest level (let's call it column X). There could be 30,000 + of these.
GIAABCD
GIABDEF
GIACCCCC
GYEFFFADD
etc

How do I return the % from the key of the 'parents' to the 'children' on the data sheet.
In the above example I would want the following (note the text string in the 'key' must be FULLY CONTAINED within the text string on the data sheet).

GIAABCD 50%
GIABDEF 100%
GIACCCCC Not found
GYEFFFADD 80%

I'm keen to avoid creating a function or macro as I want to pass on the process....

Any help would be MUCH appreciated. I'm thinking maybe a series of nested IFs / matches but it would be cumbersome and there will be alot of these so need to be easy on the memory !

By the way, I'm using Excel 2002...
Many thanks

 

[Krishnakumar]

Hi,

Not an elegant one..

In C1 on sheet1 and copied down,

=INDEX(Sheet2!$A$1:$A$4,MATCH(A1&"*",Sheet2!$A$1:$A$4,0))

In B1 on Sheet2 and copied down,

=IF(ISNUMBER(MATCH(A1,Sheet1!$C$1:$C$3,0)),INDEX(Sheet1!$B$1:$B$3,MATCH(A1,Sheet1!$C$1:$C$3,0)),"Not Found")

adjust the range.

HTH

 

[barry houdini]

In your examples the match is always within the leftmost n characters, if that 's always the case then with your data starting at X2 try this formula in Y2 copied down

=LOOKUP(9.9E+307,MATCH(Sheet1!$A$1:$A$10&"*",X2,0),Sheet1!B$1:B$10)

or if the match can be anywhere in the string

=LOOKUP(2^15,SEARCH(Sheet1!A$1:A$10,X2),Sheet1!B$1:B$10)

adjust ranges as required

You'll get #N/A if there's no match, although it's possible to adjust the formulas to show "Not found"....

 

[df9864]

Krishna - thanks for the response but this doesn't quite get me there - the longer references on the data sheet are not unique and could be in any order / character length - therefore it doesnt help them to pull them back into the 'key' tab to then match to.

Barry - I don't understand the first bit of your formula - 9.9E+307 ? What does that little number do ?

 

[df9864]

=LOOKUP(9.9E+307,MATCH(Sheet1!$A$1:$A$10&"*",X2,0),Sheet1!B$1:B$10)

Barry - you a genius... I'm no Excel slouch but I just can't get my head around this formula... I don't get the little number at the start and everything seems the wrong way 'round. I'd be grateful if you explain this little gem...
Also - I'm not sure why it fails if I extend the raise to the 'key' tab beyond the end of the populated range (ie. to include blank cells)
thanks mate.

 

[barry houdini]

In fact there's no need for 9.9E+307, you can use 2, i.e.

=LOOKUP(2,MATCH(Sheet1!$A$1:$A$10&"*",X2,0),Sheet1!B$1:B$10)

In the MATCH function

MATCH(Sheet1!$A$1:$A$10&"*",X2,0)

each value in column A of your "key", with a wildcard appended is matched against X2, as X2 is clearly a single cell then there are only 2 possible results, 1 (when there's a match) or #N/A (when there isn't), so if A3 matches you get this array

{#N/A;#N/A;1;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A}

When you lookup 2 in such an array, because 2 is always greater than any number in the array, it matches with the last numeric value, i.e. the 1, and the corresponding value is returned from column B

Note that you shouldn't have blanks in the key range otherwise you might get erroneous results

 

[barry houdini]

.....and if you want it to work with blanks in the key range you can use this version

=LOOKUP(2,1/MATCH(Sheet1!A$1:A$10&"*",X2,0)/(Sheet1!A$1:A$10<>""),Sheet1!B$1:B$10)

 

[df9864]

Barry - genius...
Is there anyway to ignore blank values so I don't have to maintain the range in case it grows on the key ?