Extracting value froma table

[KP]

I have a table with three variables. I need to extract data based on the
value of three variables. For example:

Column 1 Column2 Column3 Column4 and so on...
4 5
10 8 8-0.25 8-0.375
10 12 10-0.25 10-.0375
12 8 9-0.188 9-0.375
12 12 11-0.375 12-0.25

Based on the above data, I would like to extract data when column1 is 10,
column2 is 12 and value in the first row is 5. The answer should be
10-0.375. I have tried index, match and vlookup. They only give answer for
two variables. Please help. Thanks

 

[Toppers]

Hi,
Try this:

Enter as UDF function - for example put =XMATCH(A3,D1) in a cell for your
example.

From VBA:

MyAns=XMATCH(Range("A3"),Range("D1")

You will need to change DataRng and ColRng to suit your needs.

HTH


Function XMatch(ByVal rng1 As Range, ByVal rng2 As Range) As String

Dim lastrow As Long
Dim v() As Variant
Dim DataRng As Range, ColRng As Range

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
i = 0
For r = 2 To lastrow ' <=== Create data for row matching
ReDim Preserve v(i)
v(i) = Cells(r, 1) & Cells(r, 2)
i = i + 1
Next r

rowVal = rng1.Value & rng1.Offset(0, 1).Value ' <=== Row indices
colVal = rng2.Value ' Column index
Set DataRng = Range("c2:z50") ' Data table
Set ColRng = Range("c1:z1") ' Column Range

If Application.And(Application.Match(rowVal, v, 0),
Application.Match(colVal, Range("C1:Z1"))) Then
XMatch = Application.Index(DataRng, Application.Match(rowVal, v, 0),
Application.Match(colVal, ColRng))
Else
XMatch = "Not found"
End If

 

[flummi]

I prefer formulae wherever I can because it's easier to move stuff
around because Excel takes care of the references.

Here's a proposal (data starts in A3):

col1 col2 col3 col4 col5 result value in col1 value in row1
4 5 10 4
10 8 8-0,25 8-0.375 8-0,25
10 12 10-0,25 10-.0375 10-0,25
12 8 9-0,188 9-0.375
12 12 11-0,375 12-0.25

Formula in F3 (copied down as required):

=IF(AND(A3=$G$2;NOT(ISNA(MATCH($H$2;$C$2:$F$2;0))));OFFSET(A3;0;MATCH($H$2;$C$2:$F$2;0)+1);"")

Hope this makes sense?

Hans

Sorry if the formatting is messed up. Don't know how to get it right.