writing array code

[JohnSky]

I have the chart contain of the number of infornmation.
the Chart contains 3 Columns (Error )and 3 Rows (Speed).
example:

0-10 | 11-20 | 21-35|
------|-----|--------|------|
10-20 | 10 | 20 | 30 |
------|-----|--------|------|
30-40 | 5 | 6 | 8 |
------|-----|--------|------|
45-50 | 25 | 30 | 40 |
----------------------------|

There's also have two fields for user to input into A1
cell and A2 cell. A1 cell refer to the Column(Error) and
A2 refer to the Rows(Speed). If the user input the Value
into A1 Cell(Error) as number 8 and A2(Speed) as number
35. Then the answer will be number 5. How do I write the
code to check the range number in the chart. I hope I
clarify good.

Any help would be very appreciated.

 

[Frank Kabel]

Hi John
try the following:
- change your spreadhseet layout to something like

A B C D
1 0 11 21
2 10 10 20 30
3 30 ... ...
4 45

you see i've used only the lower boundary of your range
- lets assume this lookup range is on sheet1 and your lookup values are
on sheet2 (in cells A1, A2)
- use the following formula
=INDEX('sheet1'!A110,MATCH(A1,'sheet1'!A11,1),MATCH(A2,'sheet1'!A1:
A10,1))

 

[Anders S]

Hi JohnSky,

With the data table in D1:G4 like

D E F G
1 0 11 21
2 10 10 20 30
3 30 5 6 8
4 45 25 8 40

you can try this formula

=INDEX(D1:G4,MATCH(B1,D24)+1,MATCH(A1,E1:G1)+1)

Please note that only the lower value of the speed/error ranges shall be entered.

HTH
Anders Silven

 

[Dick Kusleika]

JohnSky

If you can't change your row and column headings, you could use a
user-defined function like this

=GetTable(A1,A2,A47)

Function GetTable(lErr As Long, lSpd As Long, rTbl As Range) As Long

Dim cell As Range
Dim FindDash As Long
Dim FndCol As Long
Dim FndRow As Long

For Each cell In rTbl.Rows(1).Cells
FindDash = InStr(1, cell.Value, "-")
If FindDash > 0 Then
If lErr >= Val(Left(cell.Value, FindDash - 1)) And _
lErr <= Val(Right(cell.Value, Len(cell.Value) - FindDash)) Then

FndCol = cell.Column
Exit For
End If
End If
Next cell

For Each cell In rTbl.Columns(1).Cells
FindDash = InStr(1, cell.Value, "-")

If FindDash > 0 Then
If lSpd >= Val(Left(cell.Value, FindDash - 1)) And _
lSpd <= Val(Right(cell.Value, Len(cell.Value) - FindDash)) Then

FndRow = cell.Row
Exit For
End If
End If
Next cell

GetTable = rTbl.Parent.Cells(FndRow, FndCol).Value

End Function