lookup-search not find Number

[majidsiddique]

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.

[kersplash]

Can you attach a workbook rather than an image?

Go Advanced -> Manage Attachments -> Upload

[majidsiddique]

OK. kindly also tell me break the String. which is mention in sheet

[Bo_Ry]

Please try

=LOOKUP(2,1/SEARCH(G3,Sheet2!C2:C7),Sheet2!C2:C7)

[majidsiddique]

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

[GeoffW283]

OK. kindly also tell me break the String. which is mention in sheet

Assuming you mean sheet1 columns I and J:

To break the ITEM strings, put the following in I2

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:

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

[majidsiddique]

Thanks Geoffw283,
its work great, super,
thanks a lot, BUT this working without array. i works proper. i apply it b2 and c2

[majidsiddique]

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)

[majidsiddique]

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

[majidsiddique]

i mean still not find result

[Bo_Ry]

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)),),))

[majidsiddique]

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.

[Bo_Ry]

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,

[Glenn Kennedy]

Please post the actual sheet from Post 12. It is NOT the same as the one posted earlier.

[Glenn Kennedy]

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))),"")

[majidsiddique]

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.

[majidsiddique]

Hi Glenn,
Thanks a lot to give me solution

[Glenn Kennedy]

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.