Assign a VLOOKUP to cell with SELECT CASE (EXCEL)

[ps40life]

Hello,
In an Excel spreadsheet, I used a nested IF statement in each cell to do this, but the number of variables has increased and I think I have to go VBA with it.

I have one cell that is assigned a cost factor. It is determined by checking a combination of 2-3 different cells. E.g., If bldg is T and Surface is Rock or Pavers, then use factor table A.

I have looked at setting up a Select Case to cover my criteria, but can I use a combination of cells that may change throughout the statement and how do I assign the VLookup to the cells? Do I use For...Each? At what time does the code execute? I need it to be updated at all times.

Example of data:
Bldg Type Surface Membrane
T Rock
O Liquid FA
T Pavers FA
O Modbit
O Bur

A few possibilities of criteria:
If Bldg is T and Rock then Lookup table 1
If Bldg is O and Liquid and FA, then Lookup table 2
If Bldg is T and Pavers or Stone, then Lookup table 3
If Bldg is O and Modbit or Bur then Lookup table 4.

I am new at using VBA in Excel and I don't understand how you assign things to a range of cells. Thanks in advance for an advice.

 

[SkipVought]

Hi,

I can't tell for sure from your table example, but it loos like you could use Select Case to do something like this...

Select Case sBldg & sType & sSurface
  Case "TRock"

  Case "OLiquidFA"

  Case "TPaversFA", TStoneFA"

  Case "OModbit", "OBur"

End Select

where you have variables named sBldg, sType & sSurface

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

Before you run off looking for code, let me try and interest you in the INDIRECT function and range names

Lets say you have a cell named "Trigger"

You also have a lookup table with all combinations of criteria concatenated together so you would have
TRock
OLiquid
etc etc
next to these (in cells to the right) is the lookup table to be used in vlookup so:

TRock table1
OLiquidFA table2
etc etc
Lets name this table TblToUse
in the "Trigger" cell have something like

=vlookup(A1&B1&C1,TblToUse,2,false)

where A1, B1 and C1 hold your variable criteria

Therefore, the text in "Trigger" will read something like "Table1" or "Table2"......still with me ???

You will then need to NAME your lookup tables as they are in TblToUse

Finally, your last vlookup formula would be
=vlookup(D1,indirect(Trigger),3,false)
or similar

Indirect converts text to a range so if you have a named range called Table1, and you have "Table1" in A1,

=Vlookup(B1,Table1,2,false)
would be the same as
=Vlookup(B1,indirect(A1),2,false)

Obviously not useful where there is only one range to lookup in but VERY useful where you have changing lookup ranges

If you have trouble following this, please post your email and I'll send a sample workbook

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[ps40life]

To XLBO:
I think I understand your idea and I wouldn't mind skipping code at all! But what happens when the criteria is only in two of the cells, and not the third? I always take into account the bldg type, but the surface and membrane may or may not be included. (I am sorry, my little data table was not as clear as it should be. The fields I included are Bldg. Type, Surface, and Membrane.)

To SKIPVOUGHT:
Same thing as above. How do I leave out a field if it is not part of criteria? Secondly, how do I assign this to the cell?

Thanks for your time and expertise.

 

[xlbo]

As long as you have all possible values in your lookup table, it shouldn't matter
Lets say
Bldg. Type in A1
Surface in A2
Membrane in A3
In A4 enter
=A1&A2&A3
This will concatenate all criteria together so as long as you have all the combinations in your table, you should be fine



Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[ps40life]

XLBO:
I have tried your suggestion and I think in a situation where all the criteria columns were the same, it would work very well. My problem is that most of the criteria only look at one column. So, in order to cover all possibilities, I would have to build a table for many criteria that are not really pertinent to the function (columns S and N). It seems like it would add a bunch of extra work that doesn't apply to my application.

I am including my IF statement:
=IF(AND(L2="Singleply",OR(S2="ROCK",S2="STONE",S2="PAVERS&quot),VLOOKUP(AG2,Factors!$G$9:$H$12,2),IF(L2="metal",VLOOKUP(AG2,Factors!$A$2:$B$5,2),IF(AND(L2="singleply",N2="fa",S2="coated&quot,VLOOKUP(AG2,Factors!$D$9:$E$12,2),IF(AND(L2="singleply",N2="fa&quot,VLOOKUP(AG2,Factors!$A$9:$B$12,2),IF(OR(L2="bur",L2="modbit&quot,VLOOKUP(AG2,Factors!$J$2:$K$5,2),IF(L2="liquid",VLOOKUP(AG2,Factors!$D$2:$E$5,2),IF(L2="steep",VLOOKUP(AG2,Factors!G2:H5,2),"No Factor&quot))))))

Notice that most of the criteria just looks at the "L" column. This works well, except the fact that I need one more criteria, and that puts me over the limit of nested statements. That's why I was looking at maybe doing a Select Case.

Any suggestions? I hate to leave out one criteria even though there are only two entries in the list at this point. Still, it's an opening for errors.

 

[Zathras]

ps40life: What you are asking for is called a "Decision Table" where you can specify various conditions and the first entry that matches the conditions is selected for action. It allows for the possibility of a condition that says "I don't care." (E.g. when L2 is "singlyply" and N2 is "fa" you have a particular action for "coated" but otherwise you don't care what is in S2.) "Action" in this case is "select a lookup table to use."

So if you have a decision table set up this way: (I put it in L5:O14 to keep it close by the input values for testing, but it can go anywhere.)

       L         M      N           O
 5

[blue]

     L2        N2     S2

[/color]

 6

[blue]

  Singleply          ROCK      Factor1

[/color]

 7

[blue]

  Singleply          STONE     Factor1

[/color]

 8

[blue]

  Singleply          PAVERS    Factor1

[/color]

 9

[blue]

  metal                        Factor2

[/color]

10

[blue]

  singleply    fa    coated    Factor3

[/color]

11

[blue]

  singleply    fa              Factor4

[/color]

12

[blue]

  bur                modbit    Factor5

[/color]

13

[blue]

  liquid                       Factor6

[/color]

14

[blue]

  steep                        Factor7

[/color]
Now, name the seven VLOOKUP ranges on the Factors sheet to some meaningful names. (I used Factor1 thru Factor7, but you should be able to do better since you know what data you are working with.) Of course, if you want to, you can have a default table by including a row with nothing in the first three columns and the default table name in the last column.

Proceed to create a User-Defined function (UDF) like this:
[blue]

Option Explicit

Function VlookupSpecial(ARange As Range) As String
Application.Volatile

[green]

' First row of range is cell addresses for all but last column
' Rows 2 thru n are conditions where blank means "Don't care"
' Last column contains string to be returned.

[/color]

Dim nCol As Integer
Dim nRow As Long
Dim sTest As String
Dim sKeys As String

  With ARange
    For nRow = 2 To .Rows.Count
      sTest = ""
      sKeys = ""
      For nCol = 1 To .Columns.Count - 1
        If .Cells(nRow, nCol) <> &quot;&quot; Then
          sTest = sTest + UCase(Range(.Cells(1, nCol)))
          sKeys = sKeys + UCase(.Cells(nRow, nCol))
        End If
      Next nCol
      If sTest = sKeys Then
        VlookupSpecial = .Cells(nRow, .Columns.Count)
        Exit Function
      End If
    Next nRow
  End With
End Function

[/color]
Then you can put this function in a work cell (I used O4):
[blue]

   =VlookupSpecial(L5:O14)

[/color]
And finally, you can put this formula in place of your nested IF statements:
[blue]

   =IF(O4="","No Factor",VLOOKUP(AG2,INDIRECT(O4),2))

[/color]