Dynamic Row Formula

[Dave Y]

Hello,

I have a spreadsheet with multiple worksheets. I am trying to get specific
data from a named range (LOANS) in one of the sheets into a cell on a
different sheet. My main sheet (named DATA)contains info such as Name, Acct,
LoanBal, and NetBal and lists all customers. The other sheet (named INFO)
contains only certain customers meeting a particular rating number. I want to
have a formula where I can bring back the specific data that I need on the
INFO sheet. The INFO sheet has only the Name and Acct existing and I want to
return the LoanBal for the matching Name and Acct. If I enter the formula of
=INDEX(LOAN,72,6) it will bring back the exact LoanBal that I need. The
problem is for the ROW value I manually typed 72 (where the correct LoanBal
is located). This of course can change as the report gets updated. The column
value will stay as 6. How can I create a dynamic formula for the ROW criteria
so that it will know which row is needed? For example; a formula that would
return 72 where I entered it manually. I have already tried using the MATCH
function but I must be doing something wrong because I cannot make it work.
Any suggestions or examples will be greatly appreciated.

 

[ShaneDevenshire]

Hi Dave,

A question - does the account # uniquely identify the line you need? or do
you need both the account # and name to tell which row?

Match will work in the first case, but then so will VLOOKUP. In the second
case you will need something more complicated:

If you are bringing back numerical data then:
=SUMPRODUCT((Sheet1!$A$1:$A$23=Sheet2!A1)*(Sheet1!$B$1:$B$23=Sheet2!B1)*Sheet1!C1:C23)

If its text data then:
{=INDEX(Sheet1!$A$1:$C$23,MAX((Sheet1!$A$1:$A$23=Sheet2!A1)*(Sheet1!$B$1:$B$23=Sheet2!B1)*(ROW(Sheet1!$B$1:$B$23))),3)}
which is array entered.

 

[Dave Y]

Hi Shane,

The acct number is unique and I think that is all I need to identify the row
to get me the LoanBal that i need. I will play with the formulas(s) that you
have provided tomorrow when I get back to work. Thank you for the quick
reply. I really appreciate it.