Hi All,
i am using a formula,
=LOOKUP(2,1/SEARCH(Sheet2!A2:A7,G3),Sheet2!A2:A7)
result is #N/A.
can anybody tell me why this? snap is attached
thanks.
Hi All,
i am using a formula,
=LOOKUP(2,1/SEARCH(Sheet2!A2:A7,G3),Sheet2!A2:A7)
result is #N/A.
can anybody tell me why this? snap is attached
thanks.
Can you attach a workbook rather than an image?
Go Advanced -> Manage Attachments -> Upload
OK. kindly also tell me break the String. which is mention in sheet
Please try
=LOOKUP(2,1/SEARCH(G3,Sheet2!C2:C7),Sheet2!C2:C7)
Thanks Bo_Ry,
its working,
can you help on this formula.
=IFERROR(INDEX(Sheet2!$A$2:$A$15,SMALL(IF(Sheet2!$B$2:$B$15=C2,ROW(Sheet2!$A$2:$A$15)-ROW($A$2)+1),ROWS($1:1))),"")
Array Formula.
to find out consecutive items. apply on this sheet G2, BUT G7 break this formula result ZERO. delete all blank line
Assuming you mean sheet1 columns I and J:OK. kindly also tell me break the String. which is mention in sheet
To break the ITEM strings, put the following in I2
Formula:Please Login or Register to view this content.
It is an array formula so must be committed with CTRL-SHIFT-ENTER
Put the following in J2:
Formula:Please Login or Register to view this content.
It is an array formula so must be committed with CTRL-SHIFT-ENTER
Copy these formulas down as required
Thanks Geoffw283,
its work great, super,
thanks a lot, BUT this working without array. i works proper. i apply it b2 and c2
hi Bo_Ry,
there is a little problem in lookup,
it takes value last no of item, like 30018 search item from range but i want specific it condition. i use it no work,
=lookup(2,1/(sheet2!b2:c15=c2)/(Sheet2!c2:c15=d2)/serach(g3,Sheet2!C2:C7),Sheet2!C2:C7)
Hi Bo_Ry,
i got it and search criteria based OK, BUT i am still achievement in below.
=IFERROR(INDEX(Sheet2!$A$2:$A$15,SMALL(IF(Sheet2!$B$2:$B$15=C2,ROW(Sheet2!$A$2:$A$15)-ROW($A$2)+1),ROWS($1:1))),"")
Array
i mean still not find result
I only see 1 match
Please try
=LOOKUP(2,1/(Sheet2!$B$2:$B$35=C3)/(Sheet2!$D$2:$D$35=D3)/SEARCH(G3,Sheet2!$C$2:$C$35),Sheet2!$C$2:$C$35)
or index with the exact match.
=INDEX(Sheet2!$C$2:$C$35,MATCH(1,INDEX((Sheet2!$B$2:$B$35=C3)/(Sheet2!$D$2:$D$35=D3)/ISNUMBER(SEARCH(G3,Sheet2!$C$2:$C$35)),),))
thanks a lot, BUT i got it,
i want below,
=IFERROR(INDEX(Sheet2!$A$2:$A$15,SMALL(IF(Sheet2!$B$2:$B$15=C2,ROW(Sheet2!$A$2:$A$15)-ROW($A$2)+1),ROWS($1:1))),"")
still not working.
this formula give actual item paste in front of invoice from sheet2. see the snap, why this break at G8. see G2 exact pick
right item. i use on sheet2A2 so you can use it sheet2C2.
Yes, sir
Kindly try with Ctrl+Shift+Enter
=IFERROR(INDEX(Sheet2!$A$2:$A$15,SMALL(IF(Sheet2!$B$2:$B$15=C2,ROW(Sheet2!$A$2:$A$15)-ROW($A$2)+1),COUNTIF(C$2:C2,C2))),"")
Regard,
Please post the actual sheet from Post 12. It is NOT the same as the one posted earlier.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
non-array alternative:
=IFERROR(INDEX(Sheet2!$A:A,AGGREGATE(15,6,ROW(Sheet2!$A$2:$A$15)/(Sheet2!$B$2:$B$15=C2),COUNTIF(C$2:C2,C2))),"")
ok, please see the attachment,
Thanks a lot Bo_Ry, its working perfect. thanks again.
is it possible by lookup formula by this. lookup value (-1) criteria same.
Hi Glenn,
Thanks a lot to give me solution
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks