Quote Matrix
 

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 |
+-------------------------------------------------------------------+

--
blyr1020
------------------------------------------------------------------------
blyr1020's Profile: http://www.excelforum.com/member.php...o&userid=31742
View this thread: http://www.excelforum.com/showthread...hreadid=514552

Quote Matrix
 

Roger,

With formulas,

=INDEX(QuoteRange,MATCH(ColSearchTerm,ColumnOfLabe ls,False),MATCH(RowSearchTerm,RowOfLabels,False))

With VBA, it would be

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


HTH,
Bernie
MS Excel MVP
"blyr1020" wrote in
message ...

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 |
+-------------------------------------------------------------------+

--
blyr1020
------------------------------------------------------------------------
blyr1020's Profile:
http://www.excelforum.com/member.php...o&userid=31742
View this thread: http://www.excelforum.com/showthread...hreadid=514552

Quote Matrix
 

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,J1 3:Z14,FALSE))

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

Roger


--
blyr1020
------------------------------------------------------------------------
blyr1020's Profile: http://www.excelforum.com/member.php...o&userid=31742
View this thread: http://www.excelforum.com/showthread...hreadid=514552

Quote Matrix
 

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 should
return a line speed of 265. Any ideas of what I have wrong?

Roger


--
blyr1020
------------------------------------------------------------------------
blyr1020's Profile: http://www.excelforum.com/member.php...o&userid=31742
View this thread: http://www.excelforum.com/showthread...hreadid=514552

Quote Matrix
 

Try setting the third parameter of each MATCH to False:

=INDEX(J17:Z44,MATCH(D4,G17:H43,False),MATCH(E4,J1 3: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,J1 3:Z13,False))

HTH,
Bernie
MS Excel MVP


"blyr1020" wrote in message
...

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 should
return a line speed of 265. Any ideas of what I have wrong?

Roger


--
blyr1020
------------------------------------------------------------------------
blyr1020's Profile: http://www.excelforum.com/member.php...o&userid=31742
View this thread: http://www.excelforum.com/showthread...hreadid=514552

Quote Matrix
 

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

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

Thank you very much for your time.
Roger


--
blyr1020
------------------------------------------------------------------------
blyr1020's Profile: http://www.excelforum.com/member.php...o&userid=31742
View this thread: http://www.excelforum.com/showthread...hreadid=514552