Convert VBA solution to a formula
 

I have a table in columns A to F (but assume table size can vary). Column A
contains codes and columns B to F contain numeric data. This numeric data is
organised in ascending sequence (B to F) within each row.

I have a VBA (UDF) function which works fine but I am interested to know if
a possible solution exists using a formula.

What is required is to match a given code with codes in column A (to give
the ROW) and then to match a lookup value with the numeric data in columns B
to F for the ROW.

The first match (if indeed the MATCH function is used) will have a
"match_type=0" and the second match a "match_type=1" i.e. return a value less
than or equal to the "lookup_value".

My "problem" is that I can't figure out how to cater for the "dynamic" row
in the second Match.

Thanks in advance.

Convert VBA solution to a formula
 

I think you need something like:
=MATCH(I4,OFFSET(B1,MATCH(I3,A:A)-1,0,1,5),0)
(I've used I3 to contain the code to match in column A and I4 to contain the
value to match in the corresponding row).
The general idea is that the innermost match function tells you what row you
want to search. The offset function returns the range of cells where you're
going to look for the number. The outer match figures out which column w/in
that row contains the number. You'll want to tweak this based on where your
input values are, whether you want exact matches or not, and what you want
returned upon matching the value.

"Toppers" wrote:

I have a table in columns A to F (but assume table size can vary). Column A
contains codes and columns B to F contain numeric data. This numeric data is
organised in ascending sequence (B to F) within each row.

I have a VBA (UDF) function which works fine but I am interested to know if
a possible solution exists using a formula.

What is required is to match a given code with codes in column A (to give
the ROW) and then to match a lookup value with the numeric data in columns B
to F for the ROW.

The first match (if indeed the MATCH function is used) will have a
"match_type=0" and the second match a "match_type=1" i.e. return a value less
than or equal to the "lookup_value".

My "problem" is that I can't figure out how to cater for the "dynamic" row
in the second Match.

Thanks in advance.

Convert VBA solution to a formula
 

Thank you very much.

It works fine ... I'll just need to study the formula a little more to
ensure I REALLY understand it!

Thanks again ... much appreciated.

"bpeltzer" wrote:

I think you need something like:
=MATCH(I4,OFFSET(B1,MATCH(I3,A:A)-1,0,1,5),0)
(I've used I3 to contain the code to match in column A and I4 to contain the
value to match in the corresponding row).
The general idea is that the innermost match function tells you what row you
want to search. The offset function returns the range of cells where you're
going to look for the number. The outer match figures out which column w/in
that row contains the number. You'll want to tweak this based on where your
input values are, whether you want exact matches or not, and what you want
returned upon matching the value.

"Toppers" wrote:

I have a table in columns A to F (but assume table size can vary). Column A
contains codes and columns B to F contain numeric data. This numeric data is
organised in ascending sequence (B to F) within each row.

I have a VBA (UDF) function which works fine but I am interested to know if
a possible solution exists using a formula.

What is required is to match a given code with codes in column A (to give
the ROW) and then to match a lookup value with the numeric data in columns B
to F for the ROW.

The first match (if indeed the MATCH function is used) will have a
"match_type=0" and the second match a "match_type=1" i.e. return a value less
than or equal to the "lookup_value".

My "problem" is that I can't figure out how to cater for the "dynamic" row
in the second Match.

Thanks in advance.