software

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: www.excelforum.com/attachment.php?postid=4382 |
-------------------------------------------------------------------

--
blyr1020
------------------------------------------------------------------------
blyr1020's Profile: www.excelforum.com/member.php...oamp;userid=31742
View this thread: www.excelforum.com/showthread...hreadid=514552Roger,

With formulas,

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

With VBA, it would be

Range(quot;QuoteRangequot;)(Application.Match(ColSearchTer m,
Range(quot;ColumnOfLabelsquot;),False),
Application.Match(RowSearchTerm,Range(quot;RowOfLabels quot;),False)).ValueHTH,
Bernie
MS Excel MVP
quot;blyr1020quot; gt; wrote in
message ...
gt;
gt; I am trying to set up a matrix (see attached) that will search a range
gt; (GT and LT) in columns and rows and return a value from the table where
gt; the X and Y search will intersect (line speed). The look-up needs to
gt; loop in the column until the search returns as true and then satisfies
gt; the row search. My excel in VBA is limited and have tried to resolve
gt; with formulas but have been unsuccessful.
gt;
gt; Thanks in advance for any assistance
gt; Roger
gt;
gt;
gt; -------------------------------------------------------------------
gt; |Filename: Quote Matrix_Test..txt |
gt; |Download: www.excelforum.com/attachment.php?postid=4382 |
gt; -------------------------------------------------------------------
gt;
gt; --
gt; blyr1020
gt; ------------------------------------------------------------------------
gt; blyr1020's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31742
gt; View this thread: www.excelforum.com/showthread...hreadid=514552
gt;

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: www.excelforum.com/member.php...oamp;userid=31742
View this thread: www.excelforum.com/showthread...hreadid=514552
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: www.excelforum.com/member.php...oamp;userid=31742
View this thread: www.excelforum.com/showthread...hreadid=514552Try 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 MVPquot;blyr1020quot; gt; wrote in message
...
gt;
gt; Sorry, my reply should have read.
gt;
gt; I entered the following and the value returned #N/A
gt;
gt; *=INDEX(J17:Z44,MATCH(D4,G17:H43),MATCH(E4,J1 3:Z14))*
gt;
gt; not =INDEX(J17:Z44,MATCH(D4,G17:H43,FALSE),MATCH(E4,J1 3:Z14,FALSE))
gt;
gt; The request was entered as Gauge = .031 and Width = 50 which should
gt; return a line speed of 265. Any ideas of what I have wrong?
gt;
gt; Roger
gt;
gt;
gt; --
gt; blyr1020
gt; ------------------------------------------------------------------------
gt; blyr1020's Profile: www.excelforum.com/member.php...oamp;userid=31742
gt; View this thread: www.excelforum.com/showthread...hreadid=514552
gt;

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: www.excelforum.com/member.php...oamp;userid=31742
View this thread: www.excelforum.com/showthread...hreadid=514552