Highline Excel Class 45: INDEX function & MATCH function 12 Unusual Examples

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

Hi everybody,

Another interesting dilemma to solve.

Using this formula:
Code:

=HYPERLINK("http://www.website.com/registry_images/" & C4 & "/" & G4 & "/" & C4 & "_" & INT(LOOKUP(9.99E+307,--MID(D4,MIN(FIND({1,2,3,4,5,6,7,8,9,0},D4&1234567890)),ROW(INDIRECT("1:"&LEN(D4)))))) & ".jpg")

from the thread: http://www.excelforum.com/excel-misc...ing-cells.html

I need to replace D4 with something along the lines of:

Code:

=IF(INDEX(Codes!A:D,MATCH(A3258,Codes!A:A,0),3)=0,A3258,INDEX(Codes!A:D,MATCH(A3258,Codes!A:A,0),4))

I am making this much harder than needed as a lot of the information in the cells are not entered directly into the cell, but functions referencing other worksheets. Any ideas on how to go about this? I am sort of familiar with the INDEX&MATCH functions but wasn't sure how to insert it into the HYPERLINK function.

Thanks,
Chris

I have got 2 worksheets in my excel file. One is the ordering database which allows colleagues to enter the data every time when they are ordering stocks, e.g. stock ID number, quantity, Payroll number, etc.. and the ordering note (print it out and send it to warehouse) is the other worksheet. My boss asked me to link the two database together, does anyone knows how to do this by combining the match and index function. His expectation is automatically fill in the whole ordering note by just entering a reference number into a box. Anyone can help? Many thanks indeed^^

I have a spreadsheet with a data table on one sheet and would like to have an Index/Match result in a particular cell on worksheet 2 based on 2 cell references on that same sheet. I am using the array fucntion but can not get the data to pouplate to the second sheet. It performs correctly if the formula is on the same sheet with the data table. Is this possible?

Here is my formula so far;

INDEX('Data 1'!$F$2:$F$101,MATCH(1,(B5='Data 1'!$B$2:$B$101)*(D5='Data 1'!$A$2:$A$101),0))

Hello, I have an array of data which has duplicates that exist in this data range. My current formula only looks at the cell below the lookup value. How can I change the following formula to search the entire array and return a "yes" or "NO?"

Code:

=IF(INDEX(C:C,MATCH(C3,C:C,0),0)C4,"YES","NO")

Any assistance would be greatly appreciated.

I have a list of 300+ account numbers with corresponding names in the next column (A and B)
On another tab an updated version of the account numbers and names appear.
Some account numbers may remain and keep or change the account names according to the new table.
I need a way to check for new accounts and/or new names and update the destination list with both without repeating the account number.
The account names will not be duplicated within the list with a different account number.
I've included it here as a comma separator. The left is the current account list and the right side is the newer account list.
A,B,C,D,E
1000001,Legacy Cash #123,,1000001,Legacy Cash #123
1000002,Legacy Cash #234,,1000005,Legacy Cash #987
1000003,Bank Acct #123,,1000003,Bank Acc #123
1000004,Bank Acct #345,,1000004,Bank Acct 345
,,,1000002,Legacy Cash #234
All Numbers are 7 digits

Hi, I need help in writing functions maybe for example to find if named sheet exist with x name...etc if not do this else that...or
to see if col has dates...
My actual question would be to learn how to write this kind of function.

Thanks in advance.
Pedie

does anyone can help me in this issues?
I just read the MS Training but i didn't get it.
Will be better if you can help me with examples and more simple explanations

Hi in below stated code i do get this error:
Unable to Match property of Worksheet class function.
It seems I made everything right, but I cannot figure out why this message appears. Hope you can find out the problem...

Code:

Private Sub StudentButton_Click()
    Dim DateField As Range, ResultTable As Range
    Dim StartDay As Date, EndDay As Date
    Dim lRow As Long, lColumn As Long
    Dim sDayPos As Long, eDayPos As Long
    Dim StartRow As Integer, StartColumn As Integer
    Dim MyMonth As Integer, MyYears As Integer, lDate As Integer

    StartRow = Range("FirstCell").Row
    StartColumn = Range("FirstCell").Column

    Set ResultTable = Range("ResultTable")
    Set DateField = Sheets(2).Range("Dates")
    
    'Getting Last column number
    With Range(StartRow & ":" & StartRow)
         lColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With

    For MyYears = 1 To lColumn - StartColumn
        For MyMonth = 1 To 12

----------------------------------------- SOME PART OF CODE BETWEEN ------------------------------

            'Getting positions for Fist day and last day of a month in Datafield Range
             With WorksheetFunction
                sDayPos = .Match(StartDay, DateField, 0)   <<<<<<< CAUSES ERROR
                eDayPos = .Match(EndDay, DateField, 0)    <<<<<<< CAUSES ERROR
            End With 

        MsgBox sDayPos & " and " & eDayPos
        Next MyMonth
    Next MyYears
End Sub

I am using the following formula

=INDEX('[Price List.xls]LIST'!$B:$B, MATCH(E32,'[Price List.xls]LIST'!$A:$A,0))

to pull data from a workbook named Price List into another by looking up the item number and supplying the correct description. This formula works fine but I want the cell to remain blank if the referenced item number is not found. I tried using the following formula with no success

=IF(INDEX('[Price List.xls]LIST'!$B:$B, MATCH(E32,'[Price List.xls]LIST'!$A:$A))=0,"",INDEX('[Price List.xls]LIST'!$B:$B, MATCH(E32,'[Price List.xls]LIST'!$A:$A)))

Any suggestions?

Thanks!