Easy formula for experienced users

[Guest]

I am trying to create a query in excel, something basic that will give me a
list of "Banks" in my case that meet certain pre-determined criteria.

Ex.

Bank A Bankruptcy Foreclosure Collections
"Yes" "No"
"Yes"

I want to be able to produce the qualifying banks that meet entered query
criteria. I have seen a few formulas that seem to be basic math, but i am
new to excel and could use experienced help.

Thanks,

IndyBroker

 

[Arvi Laanemets]

Hi

Add a sheet p.e. Report, and enter criteria as data validation lists at top
of it. Like this:
Bankruptcy Foreclosure Collections
Yes No Yes

On Sheet1, add a column Selected to left your table (column A). The formula
for A3:
=IF(AND(C2=Sheet2!$A$2,D2=Sheet2!$B$2,E2=Sheet2!$C$2),SUMPRODUCT(($C$2:$C2=$
C2)*($D$2:$D2=$D2)*($E$2:$E2=$E2)),"")
and copy it down. As result, rows meeting all criterias selected on Report
sheet will be numbered. You can hide this column now.

On sheet Report, into cell p.e. A5 enter the formula like this:
=IF(ISERROR(VLOOKUP(ROW()-4,Sheet1!$A$2:$B$100,2,)),""LOOKUP(ROW()-4,Sheet1!
$A$2:$B$100,2,0))
(adjust the lookup range for your needs)
Copy the formula dows as much you think you'll need.

 

[Frank Kabel]

Hi Arvi
for your second formula you probably meant:
=IF(ISERROR(VLOOKUP(ROW()-4,Sheet1!$A$2:$B$100,2,0)),"",VLOOKUP(ROW()-4
,Sheet1!
$A$2:$B$100,2,0))

 

[Arvi Laanemets]

Hi

Thanks, this was a typo! Accidently I pushed Insert taste on keyboard before
replaced delimiters in formula.