If statement result

[iashorty]

I have a formula that is currently in my Excel spreadsheet, but we want to
just show the result in the spreadsheet and not the formula. We also have too
many nested functions for one cell. Can I accomplish both throught VBA?

Here is my formula with too many functions:

=IF($S9=2,IF(ISERROR(INDEX(TABLE,MATCH($B9,blue,0),MATCH($T$8,HEADERS,0))),IF(ISERROR(INDEX(TABLE,MATCH($B9,brown,0),MATCH($T$8,HEADERS,0))),IF(ISERROR(INDEX(TABLE,MATCH($B9,green,0),MATCH($T$8,HEADERS,0))),IF(ISERROR(INDEX(TABLE,MATCH($B9,red,0),MATCH($T$8,HEADERS,0))),IF(ISERROR(INDEX(TABLE,MATCH($B9,yellow,0),MATCH($T$8,HEADERS,0))),"Research",INDEX(TABLE,MATCH($B9,yellow,0),MATCH($T$8,HEADERS,0))),INDEX(TABLE,MATCH($B9,red,0),MATCH($T$8,HEADERS,0))),INDEX(TABLE,MATCH($B9,green,0),MATCH($T$8,HEADERS,0))),INDEX(TABLE,MATCH($B9,brown,0),MATCH($T$8,HEADERS,0))),INDEX(TABLE,MATCH($B9,blue,0),MATCH($T$8,HEADERS,0))),"")

this formula basically finds a value in column B to various columns on
different sheets (the colors) and returns the value in the same row under the
heading in T9. It worked until I put yellow in. But, if I can put just the
result of the formula in the spread sheet would not take so long to download
the table.

 

[Joel]

Try this

call with
=GetValue($S9, $B9, $T$8)

Function GetValue(NumberValue, RowItem, ColumnItem)

If NumberValue = 2 Then
ColNumber = Range("Headers").Find(what:=ColumnItem, _
LookIn:=xlValues, lookat:=xlWhole)
If ColNumber Is Nothing Then
GetValue = "Cannot find : " & MatchColumn
Exit Function
End If

RowNumber = Range("blue").Find(what:=RowItem, _
LookIn:=xlValues, lookat:=xlWhole)
If RowNumber Is Nothing Then
RowNumber = Range("brown").Find(what:=RowItem, _
LookIn:=xlValues, lookat:=xlWhole)
If RowNumber Is Nothing Then
RowNumber = Range("green").Find(what:=RowItem, _
LookIn:=xlValues, lookat:=xlWhole)
If RowNumber Is Nothing Then
RowNumber = Range("red").Find(what:=RowItem, _
LookIn:=xlValues, lookat:=xlWhole)
If RowNumber Is Nothing Then
GetValue = "research"
Exit Function
End If
End If
End If
End If
GetValue = Range("Table")(RowNumber, ColNumber)
Else
GetValue = ""
End If
End Function