Index match

[kristipham]

Based on the user's selection of enclosure types, there are 4 different tables based off of temperature and watt/ft^2. In one spread sheet, it calculates the change in temperature (delta t) and we want it to point to the watts/ft^2 on the specific enclosure type that the user has selected. Rather than a nested If statement, we want to use the index match function. Please explain step by step which option is a better route just in case there are other enclosures added.

[/URL][/IMG]

[/URL][/IMG]

Rather than using this:
=IF(B4="Stainless Steel",VLOOKUP(B16,Sheet3!A3:B53,2,TRUE),IF(B4="ANSI 61 Gray",VLOOKUP(B16,Sheet3!D3:E57,2,TRUE),IF(B4="Aluminum",VLOOKUP(B16,Sheet3!G3:H51,2,TRUE),IF(B4="White",VLOOKUP(B16,Sheet3!J3:K37,2,TRUE)))))

 

[L. Howard]

I'm trying to duplicate the sheet 3 image, with the four enclosures types and the ensuing data for each type. The formulas and the column references c, d, f don't make sense to me. Or I am not installing them correctly.

How about a link to the workbook with two sheets that I can download.

Howard

 

[L. Howard]

Disregard the link, I believe I have constructed a suitable sheet 3 duplicate.

I don't know of a single Index/Match formula that can fit itself to multiple Index ranges along with multiple Row designators.

If you are agreeable to a VBA solution I have a sheet change event macro that will does the job. It is posted below, and will copy to sheet 2 module.

I used named eight ranges in the formulas the code inserts into cell B4 on sheet 2.

Here is how to name the ranges to suit the formulas, using Stainless Steel as an example. (the other three are done the same using their respective columns)


On sheet 3...
Select the range A4:B53 and while selected click in the Name Box and enter sSteel then Enter.
Select the range A4:A53 and while selected click in the Name Box and enter DeltaSS then Enter.

Do the same for the other three exposure types, adjusting the length of the columns to their true row lengths.

The names are used in the code formula (and on the worksheet if needed) in this manner for Stainless Steel, ANSI 61 Gray, Aluminum, White.

The named ranges are bold font in each of these formulas.

=INDEX(sSteel,MATCH($B$16,DeltaSS,1),MATCH(B4,Sheet3!$A$1:$B$1,0))

=INDEX(anGray,MATCH($B$16,DeltaAN,1),MATCH(B4,Sheet3!$D$1:$E$1,0))

=INDEX(aLumin,MATCH($B$16,DeltaAL,1),MATCH(B4,Sheet3!$G$1:$H$1,0))

=INDEX(wHite,MATCH($B$16,DeltaWH,1),MATCH(B4,Sheet3!$J$1:$K$1,0))

Here is the code, which is a change-event macro keyed to cell B4 on sheet 2. Only works when cell B4 on sheet 2 is changed, in this instance, a selection from the drop down in that cell for an exposure type.

When the change occurs, the proper formula will be placed in cell C3, which will return a watts per ft. to cell C3 as a value only. (there will be no formula in cell C3, only a value)

The location of the cell C3 is easily changed, I did not know exactly where you wanted the data returned.
Select Case method will allow you to enter additional Case Is = "???" as you need them. You would merely follow the existing format for each additional case.

Howard

The code to copy into sheet 2 sheet module.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("$B$4")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

Select Case Target.Value

  Case Is = "Stainless Steel"
    With Target.Offset(, 1)
      .Formula = "=INDEX(sSteel,MATCH($B$16,DeltaSS,1),MATCH(B4,Sheet3!$A$1:$B$1,0))"
      .Value = .Value
    End With
    
  Case Is = "ANSI 61 Gray"

    With Target.Offset(, 1)
      .Formula = "=INDEX(anGray,MATCH($B$16,DeltaAN,1),MATCH(B4,Sheet3!$D$1:$E$1,0))"
      .Value = .Value
    End With
      
  Case Is = "Aluminum"

    With Target.Offset(, 1)
      .Formula = "=INDEX(aLumin,MATCH($B$16,DeltaAL,1),MATCH(B4,Sheet3!$G$1:$H$1,0))"
      .Value = .Value
    End With
      
  Case Is = "White"

    With Target.Offset(, 1)
      .Formula = "=INDEX(wHite,MATCH($B$16,DeltaWH,1),MATCH(B4,Sheet3!$J$1:$K$1,0))"
      .Value = .Value
    End With
  
  Case Else
    MsgBox "no case"

End Select