• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lenient lookup [Advanced Formula Trick]

D

Dan_K_2014

Member
Hello,
recently Chandoo.org posted very good example of Lenient lookup [Advanced Formula Trick].
Formula is working well, but what if I have many the same amounts? How to list all Names with the same amount?
In my case Names= Cheques numbers.
.=IFERROR(INDEX(Table3[Names],IFERROR(MATCH($L$3,Table3[Amount],0), MATCH(1, (Table3[Amount]>($L$3-0.5))*(Table3[Amount]<($L$3+0.5)),0)))&IF(COUNTIFS(Table3[Amount],$L$3),"","*"),"Not found")
 
If I have,for example,15 issued checks with the same amounts, can you update my formula that I could receive a list of those checks and payees?
Thank you
 
the best the syntax: The syntax of VLOOKUP formula is:=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) To convert numbers to ranges, you use the |range_lookup| argument with a value of |TRUE|, which will tell Excel to make an approximate match to the lookup value. :) Bluestacks TextNow Photomath
 
Can you help practically with my formula that it gave me all checks having the same amount? I tried to convert myself, but it works even worse.
.=IFERROR(INDEX(DATA!$A$5:$A$5473,SMALL(IF((D5:D5474>=($L$3-0.5))*(D5:D5474<($L$3+0.5)),ROW(data)-ROW(INDEX(data,1,1))+1),ROWS($5:6))),"")
 
In L5...
=IFERROR(INDEX($A$5:$A$190,SMALL(IF(($D$5:$D$190>=$L$3-0.5)*($D$5:$D$190<$L$3+0.5),ROW($A$5:$A$190)-4),ROWS($A$1:A1))),"")

Confirmed as Array (CTRL + SHIFT + ENTER).

Copy down.
 

Attachments

  • ChqLog_sent.xls
    73.5 KB · Views: 7
You must log in or register to reply here.
Back
Top