Array Lookup

  • Thread starter Thread starter tj
  • Start date Start date
T

tj

I have an array with 8 colums and 7 rows. Each column and each row contains
a range of values (for example, a column heading is ">70% and <= 75%", and a
row heading is ">=680 and <700") and I need to use the data in the cell in
another formula. How do I write the array formula to look up the data that I
need for the other formula? The value for which column to use is a result of
a Loan-To-Value calculation in another cell in the worksheet, and the row
would be from a customer's credit score that would be entered in another cell
in the worksheet. The cells of the array would contain a value that would be
used to calculate a borrower's closing costs.

Thanks for any assistance!
 
I have an array with 8 colums and 7 rows. Each column and each row contains
a range of values (for example, a column heading is ">70% and <= 75%", and a
row heading is ">=680 and <700") and I need to use the data in the cell in
another formula. How do I write the array formula to look up the data that I
need for the other formula? The value for which column to use is a result of
a Loan-To-Value calculation in another cell in the worksheet, and the row
would be from a customer's credit score that would be entered in another cell
in the worksheet. The cells of the array would contain a value that would be
used to calculate a borrower's closing costs.

Thanks for any assistance!
Click to expand...

Assuming that your array is in B2:I8, the column headers in B1:I1 and
the row headers in A2:A8 and that your columns and rows represent
larger values to further to the right and further down respectively
you can try the following.

Replace each column header with its lower limit only.
Example: Replace the ">70% and <=75%" with just 70%.

Do the same with the rows headers.

Note: you can always add a new row for the column headers above
and a new column for the row headers to the left where you can put any
text you want. And the adjusted row and column can be hidden if not
wanted to be visible.

The formula to lookup the value is now as follows:

=OFFSET(A1, MATCH(K1, A2:A8), MATCH(L1, B1:I1))

where K1 and L1 are the cells where you have the
customer's credit score and Loan-to-value calculation results
respectively.

Hope this helps / Lars-Åke
 
Back
Top