Index match and drop down lists question

[Omer104]

Hi there,

Can anyone help with index match and drop down lists?

I am searching on another sheet say dealing sheet which has hundreds of securities and for each particular security (you have the unique code in COL A for example U488787)
and then you have information concerning that security relating to dealing = 10AM 1st Dec in COL B, 10am 2nd Dec in COL C, 10am 3rd Dec in COL D

On another sheet if i am given the security unique code U488787, it will look up in the dealing sheet - the next deal date so 10am 1st Dec i(f i specifiy COL B in the index formula.)

However Is there a way i can get a drop down that will show the results from COL B, COL C and COL D? so 10AM 1st Dec ,10am 2nd Dec ,10am 3rd Dec and then i can select the appropriate answer?

Many thanks

 

[mmhill]

If you want dropdowns, that means data validation. If you want it to be different depending on the code, then it has to be dynamic. This means you need it to react to an event - like a selection change.
But, you want to skip it all if you haven't selected a cell where you need a dropdown.

Put the code below in the worksheet module of the sheet you want dropdowns on. Set the Const values at the top for what you need. When you select a cell in a give range, it get the "unique code", finds it in the other worksheet, get however many values you want and makes a dropdown list for teh cell you are in. when you select another cell, it deletes the data validatio list for the cell you just left so you get a fresh validatio list every time. If you pick a cell outside the target range, it does nothing and quits.

Const cSourceWorksheetName$ = "SRC"
Const cLookUpColumn% = 1
Const cValuesOffset% = 1
Const cValuesToGet% = 3

Const cRangeApplyDropDown$ = "B2:B7"
Const cCodeToSearchForColumn% = 1

Dim OldCell As Range, NewCell As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strCode$, element, strValidation$

    If Not OldCell Is Nothing Then OldCell.Validation.Delete
    Set NewCell = ActiveCell

    If Intersect(Range(cRangeApplyDropDown), NewCell) Is Nothing Then Exit Sub _
    Else strCode = Intersect(Columns(cCodeToSearchForColumn), NewCell.EntireRow).Value
    
    With Worksheets(cSourceWorksheetName).Columns(cLookUpColumn)
        For Each element In .Find(strCode, , LookIn:=xlValues, Lookat:=xlWhole).Offset(0, cValuesOffset).Resize(columnsize:=cValuesToGet).Cells
            strValidation = strValidation & "," & element
        Next element
    End With
    Set element = Nothing

    With NewCell.Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=Right(strValidation, Len(strValidation) - 1)
    End With
    
    Set OldCell = NewCell

End Sub

 

[Omer104]

Thanks so much but i was hoping it was excel formula?

If i use index match on the unique security code in the other sheet (specifying COL B in Index formula and COL A for match formula where the unique security code is) I will get 10AM 1st Dec (in COL B), but i need a drop down of that and the next dealing date of 10AM 2nd Dec in COL C and so on.

 

[mmhill]

You pushed my creativity, but it can be done. There are some things to be careful with. It's a long answer, but it was an interesting problem.

The best result is ... =OFFSET(INDIRECT(\LUSheet&ADDRESS(MATCH(OFFSET(B2,0,-1,1,1),\Codes,0)+ROW(\Codes)-1,COLUMN(\Codes))),0,1,1,3)

Read on to see how to get their. It's actually not hard, I just broke it down.

The basic formula is this ...

=OFFSET( INDIRECT( "'LU'!" & ADDRESS( MATCH( OFFSET(B2,0,-1,1,1) ,LU!$A:$A,0) ,COLUMN(LU!$A:$A) ) ) ,0,1,1,3 )

OFFSET(B2,0,-1,1,1) ... the address (B2) is the addresss of the cell you want the dropdown in. The -1 is how many cells to the left the Code you will search for is in. It's better to do it this way than use direct address references. This way, no mattr where you place the validation formula, it will look 1 cell to the left for the Code. You can change that -1 number to whatever you like.

MATCH( XXX ,LU!$A:$A,0) ... You need the absolute row number of the matched code, so use the whole column. LU!$A:$A is the column you will be searching, you need to include the sheet name if it is on a different sheet. This will return what row the Code is in.

"'LU'!" & ADDRESS( XXX ,COLUMN(LU!$A:$A) ) ... gets the address of where the matched code is. If the reference will never change, you could change COLUMN(LU!$A:$A) to 1. The "'LU'!" part at the start is the sheet where you look up the code. If you change the sheet name, you will have to update this in the formula. There is a way to do this automatically.

INDIRECT( XXX ) ... changes the address string to a cell reference.

OFFSET( XXX ,0,1,1,3 ) ... returns a range that is offset form the INDIRECT cell 0 rows, 1 column that is 1 row high and 3 rows wide.

Select the cell where you want the dropdown to be. Select DataValidation and List and paste that formula into the source. Edit B2 for the current cell address and the LU! sheetname. If the sheetname has a space in it, you must surround the name with apostrophe marks like: 'Look Up'!

Other stuff ...

It's cleaner to use a named range rather than references. If you name LU!$A:$A to somethig like \Codes, that make the formula a little easier. If you want to look at a specific range rather than the whole column, name the range and add the value of the first row in the range minus 1

=OFFSET( INDIRECT( "'LU'!" & ADDRESS( MATCH(OFFSET(B2,0,-1,1,1),\Codes,0) ,COLUMN(\Codes) ) ) ,0,1,1,3 )

=OFFSET( INDIRECT( "'LU'!" & ADDRESS( MATCH(OFFSET(B2,0,-1,1,1),\Codes,0)+ROW(\Codes)-1 ,COLUMN(\Codes) ) ) ,0,1,1,3 )

If you want to be able to autoadjust if the sheetname is changed, you need to replace "'LU'!" with something to figure out the sheetname. Substitute the below for "'LU!'" ...

"'"&MID(CELL("filename",LU!$A$1),FIND("]",CELL("filename",LU!$A$1))+1,255)&"'!"

"'"&MID(CELL("filename",\Codes),FIND("]",CELL("filename",\Codes))+1,255)&"'!"

Like the other, it's cleaner to use a Name to refer to figuring out the sheet name. For the formula below, I created a name called \LUName that use the MID/CELL/FIND above to get the sheet name. The resulting formula is ...

=OFFSET(INDIRECT(\LUSheet&ADDRESS(MATCH(OFFSET(B2,0,-1,1,1),\Codes,0)+ROW(\Codes)-1,COLUMN(\Codes))),0,1,1,3)

So, name the lookup range of codes to \Codes, create a name called \LUName equal to the MID/CELL/FIND stuff above, and replace B2 with the cell address you want the dropdown in. Then, paste that formula into the Data Validation - List - Source. Copy and paste that wherever you want.

One final note - If there is no Code Lookup Match, it doesn't give an error - the dropdown just doesn't work.

 

[Automatrix]

You haven't listed your Office Version - are you able to use array formulas?

 

[Omer104]

Just wanted to massive thank you for this ! I really appreciate your help on this.

I went through but getting sadly a warning or drop down of the actual formulas I have inputted weirdly.

In your description you state XXX , what does that mean?

MATCH( XXX ,LU!$A:$A,0)

INDIRECT( XXX )

OFFSET( XXX ,0,1,1,3 )


For clarification

The Column it needs to search in is COL H (in the sheet named Cat)

The unique code I am looking for is in B33, I use this value to search in COL H in Cat sheet. I then go across 12, 13 and 14 columns to get the next 3 available answers I want. These are the value I want appearing in the drop down in CELL B37 in my main sheet.

 

[Omer104]

excel for microsoft 365

 

[mmhill]

Just wanted to massive thank you for this ! I really appreciate your help on this.

I went through but getting sadly a warning or drop down of the actual formulas I have inputted weirdly.

In your description you state XXX , what does that mean?

MATCH( XXX ,LU!$A:$A,0)

INDIRECT( XXX )

OFFSET( XXX ,0,1,1,3 )


For clarification

The Column it needs to search in is COL H (in the sheet named Cat)

The unique code I am looking for is in B33, I use this value to search in COL H in Cat sheet. I then go across 12, 13 and 14 columns to get the next 3 available answers I want. These are the value I want appearing in the drop down in CELL B37 in my main sheet.

The XXX was just referring to the lines above. I put XXX there to keep the text line short. Just ignore it. Based on the info above ...

The cell that has the unique code you are searching for (to be able to make teh drop down box) is in cell B33 on the "main" sheet
You are searching for that code in Column H of a worksheet named "Cat" (that is the 8th column on the "Cat" worksheet)
The three values that you want to show up in the dropdown will be found in the "Cat" worksheet ... in the same rows as the code that was matched in Column H. These 3 values will be in columns 12, 13, and 14 (Columns L, M and N)
You want the dropdown to appear in cell B37 on the "main" sheet

If so, select B37 on the main sheet ... in the ribbon (under the Data menu) click Data Validation. Under "Settings", select List, make sure the Ignore blank and In-cell dropdown boxes are checked and in the Source box enter ...

=OFFSET( INDIRECT( "CAT!" & ADDRESS( MATCH( $B$33,CAT!$H:$H,0 ) ,8) ) ,0,4,1,3 )

In the formula ... the 4 is important but I may have assumed wrong. If you are searching for the colde in COLUMN H, this is how many columns to the right that the values you want in the dropdown start. So, if Lookup is in H and values are in L, M and N, that number is 4 (L is column 12, H is column 8 ... 12-8 = 4). If I got it wrong, just change that number.

=OFFSET( INDIRECT( "CAT!" & ADDRESS( MATCH( $B$33,CAT!$H:$H,0 ) ,8) ) ,0,4,1,3 )

 

[Omer104]

that is absolutely brilliant.

I cannot thank you enough - I had no idea you can use drop down in such a complex fashion! Thank you mmhill!!