Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matrix Problem | Excel Discussion (Misc queries) | |||
Matrix calculation | Excel Discussion (Misc queries) | |||
How to identify entries in a matrix also present in another list | Excel Worksheet Functions | |||
Recreate the upper part of a symetric data matrix 75x75 | Excel Discussion (Misc queries) | |||
Finding Values in a "Matrix" | Excel Discussion (Misc queries) |