Quote Matrix

[blyr1020]

I am trying to set up a matrix (see attached) that will search a range
(GT and LT) in columns and rows and return a value from the table where
the X and Y search will intersect (line speed). The look-up needs to
loop in the column until the search returns as true and then satisfies
the row search. My excel in VBA is limited and have tried to resolve
with formulas but have been unsuccessful.

Thanks in advance for any assistance
Roger


+-------------------------------------------------------------------+
|Filename: Quote Matrix_Test..txt |
|Download: http://www.excelforum.com/attachment.php?postid=4382 |
+-------------------------------------------------------------------+

 

[Bernie Deitrick]

Roger,

With formulas,

=INDEX(QuoteRange,MATCH(ColSearchTerm,ColumnOfLabels,False),MATCH(RowSearchTerm,RowOfLabels,False))

With VBA, it would be

Range("QuoteRange")(Application.Match(ColSearchTerm,
Range("ColumnOfLabels"),False),
Application.Match(RowSearchTerm,Range("RowOfLabels"),False)).Value


HTH,
Bernie
MS Excel MVP

 

[blyr1020]

Thank you for the prompt reply Bernie.

I entered the following and the value returned #N/A

=INDEX(J17:Z44,MATCH(D4,G17:H43,FALSE),MATCH(E4,J13:Z14,FALSE))

The request was entered as Gauge = .031 and Width = 50 which shoul
return a line speed of 265. Any ideas of what I have wrong?

Roge

 

[blyr1020]

Sorry, my reply should have read.

I entered the following and the value returned #N/A

*=INDEX(J17:Z44,MATCH(D4,G17:H43),MATCH(E4,J1 3:Z14))*

not =INDEX(J17:Z44,MATCH(D4,G17:H43,FALSE),MATCH(E4,J1 3:Z14,FALSE))

The request was entered as Gauge = .031 and Width = 50 which shoul
return a line speed of 265. Any ideas of what I have wrong?

Roge

 

[Bernie Deitrick]

Try setting the third parameter of each MATCH to False:

=INDEX(J17:Z44,MATCH(D4,G17:H43,False),MATCH(E4,J13:Z14,False))

BUT, and this one is important, you should only have one row or column as the range for the second
parameter of your MATCH functions.

=INDEX(J17:Z44,MATCH(D4,G17:G44,False),MATCH(E4,J13:Z13,False))

HTH,
Bernie
MS Excel MVP

 

[blyr1020]

That did the trick Bernie.
=INDEX(J17:Z44,MATCH(D4,G17:G44,False),MATCH(E4,J13:Z13,False))

As you noted I didn't need the extra column and row.

Thank you very much for your time.
Roger