How to perform an advanced lookup in Excel 2016? - Microsoft Community

i have sheet containing table client information including fields enter amount of money owe me after rendering services , fields payments make! table contains info 2000 clients , have additional sheet in workbook performs lookup , return client's details still outstanding!

i make use of formula in last column of table named 'control' return "" when didn't charge them, "outstanding" if owe me money , "settled" if charged them , they've paid amount in full!

your formula 

=iferror(index(info!$a$3:$ao$2002,small(if(info!$m$4:$ap$2002="outstanding",row(info!$ap$4:$ap$2002)-row(info!$ap$2)),row(a1))),"")

select  1 column range outstanding in outstanding mentioned if column m use if(info!$m$4:$m$2002="outstanding" or if in col ap use if(info!$apm$4:$ap$2002="outstanding", same thing index part of function

if due amount in ao use ao column not entire range

 index(info!$ao$3:$ao$2002 not index(info!$a$3:$ao$2002

you have change index part of range feild want extract,dont use entire range

  

Office / Excel / Windows 10 / Office 2016