Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Find Value Based On Criteria

  1. 07-27-2011, 01:27 PM #1
    excel_learner
    excel_learner is offline
    Member
    Join Date
    Apr 2011
    Posts
    69
    Rep Power
    14

    Find Value Based On Criteria

    I have attached a excel sheet as an example, where i have in one column as a text which i want to match with another column range and bring the code.


    kindly assist.

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.eileenslounge.com/viewtopic.php?p=296754#p296754
    https://www.eileenslounge.com/viewtopic.php?p=296859#p296859
    https://teylyn.com/2017/03/21/dollarsigns/#comment-191
    https://www.excelfox.com/forum/showthread.php/2918-Right-Hand-Side-Range-Range-Value-values-Range-Range-Value-only-sometimes-Range-Range-Value-Anomaly
    https://www.excelfox.com/forum/showthread.php/2355-Tests-and-Notes-on-Range-Referrencing/page8
    https://www.eileenslounge.com/viewtopic.php?p=296859#p296859
    https://www.excelfox.com/forum/showthread.php/2355-Tests-and-Notes-on-Range-Referrencing?p=24006&viewfull=1#post24006
    https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=23185&viewfull=1#post23185
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Attached Files Attached Files
    Last edited by DocAElstein; 03-30-2024 at 03:36 AM.
    ----
    excel_learner
    (Using XL 2007 & 2010)

    -------Learning never stops!
    Reply With Quote Reply With Quote
  2. 07-27-2011, 02:15 PM #2
    Admin
    Admin is offline
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi,

    Try

    =LOOKUP(9.9999E+307,SEARCH(J2,A2:A1884),D2: D1884)

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://eileenslounge.com/viewtopic.php?p=318868#p318868
    https://eileenslounge.com/viewtopic.php?p=318311#p318311
    https://eileenslounge.com/viewtopic.php?p=318302#p318302
    https://eileenslounge.com/viewtopic.php?p=317704#p317704
    https://eileenslounge.com/viewtopic.php?p=317704#p317704
    https://eileenslounge.com/viewtopic.php?p=317857#p317857
    https://eileenslounge.com/viewtopic.php?p=317541#p317541
    https://eileenslounge.com/viewtopic.php?p=317520#p317520
    https://eileenslounge.com/viewtopic.php?p=317510#p317510
    https://eileenslounge.com/viewtopic.php?p=317547#p317547
    https://eileenslounge.com/viewtopic.php?p=317573#p317573
    https://eileenslounge.com/viewtopic.php?p=317574#p317574
    https://eileenslounge.com/viewtopic.php?p=317582#p317582
    https://eileenslounge.com/viewtopic.php?p=317583#p317583
    https://eileenslounge.com/viewtopic.php?p=317605#p317605
    https://eileenslounge.com/viewtopic.php?p=316935#p316935
    https://eileenslounge.com/viewtopic.php?p=317030#p317030
    https://eileenslounge.com/viewtopic.php?p=317030#p317030
    https://eileenslounge.com/viewtopic.php?p=317014#p317014
    https://eileenslounge.com/viewtopic.php?p=316940#p316940
    https://eileenslounge.com/viewtopic.php?p=316927#p316927
    https://eileenslounge.com/viewtopic.php?p=316875#p316875
    https://eileenslounge.com/viewtopic.php?p=316704#p316704
    https://eileenslounge.com/viewtopic.php?p=316412#p316412
    https://eileenslounge.com/viewtopic.php?p=316412#p316412
    https://eileenslounge.com/viewtopic.php?p=316254#p316254
    https://eileenslounge.com/viewtopic.php?p=316046#p316046
    https://eileenslounge.com/viewtopic.php?p=317050&sid=d7e077e50e904a138c794e1 f2115da95#p317050
    https://www.youtube.com/@alanelston2330
    https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-
    https://eileenslounge.com/viewtopic.php?p=316154#p316154
    https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg
    https://teylyn.com/2017/03/21/dollarsigns/#comment-191
    https://eileenslounge.com/viewtopic.php?p=317050#p317050
    https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854
    https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875
    https://eileenslounge.com/viewtopic.php?p=316057#p316057
    https://eileenslounge.com/viewtopic.php?p=315915#p315915
    https://eileenslounge.com/viewtopic.php?p=316705#p316705
    https://eileenslounge.com/viewtopic.php?p=316704#p316704
    https://eileenslounge.com/viewtopic.php?p=176255#p176255
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 07-27-2024 at 02:14 PM.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)
    Reply With Quote Reply With Quote
  3. 07-27-2011, 05:46 PM #3
    pjotrsk
    pjotrsk is offline
    Junior Member
    Join Date
    Jun 2011
    Posts
    1
    Rep Power
    0
    Send a message via MSN to pjotrsk
    Hi,

    I propose:
    =find_any($A$2:$D$1884;1;J2;4)

    This is user-defined formula:

    Code:
    Function find_any(res As Range, find_in As Long, find_val As String, find_out As Long)
For Each res In res
    If IsError(res.Cells.Value) Then GoTo turp
    If res.Column = find_in And InStr(res.Cells.Value, find_val) > 0 Then find_any = res(res.Row, find_out): Exit Function
turp:
Next
End Function
    Last edited by Admin; 07-27-2011 at 08:04 PM. Reason: code tag added
    Reply With Quote Reply With Quote
  4. 07-27-2011, 08:06 PM #4
    Admin
    Admin is offline
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi pjotrsk,

    Welcome to ExcelFox !

    Please use code tags while posting codes

    It's better to stick with native excel functions whenever possible.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)
    Reply With Quote Reply With Quote
  5. 07-27-2011, 11:24 PM #5
    Haseeb A
    Haseeb A is offline
    Junior Member Haseeb A's Avatar
    Join Date
    Apr 2011
    Posts
    21
    Rep Power
    0
    Hello, Also try this,

    Code:
    =INDEX(D:D,MATCH("*"&J2&"*",A:A,0))
    Last edited by Haseeb A; 07-27-2011 at 11:26 PM.
    Worksheet Functions
    Excel 2003 to Excel 2007 command reference guide
    Reply With Quote Reply With Quote
  6. 07-28-2011, 04:52 AM #6
    Rasm
    Rasm is offline
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14
    If you want a string comparison to be insensetive to case then use

    Option Compare Text ' in the declaration section of your program

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://eileenslounge.com/viewtopic.php?p=318868#p318868
    https://eileenslounge.com/viewtopic.php?p=318311#p318311
    https://eileenslounge.com/viewtopic.php?p=318302#p318302
    https://eileenslounge.com/viewtopic.php?p=317704#p317704
    https://eileenslounge.com/viewtopic.php?p=317704#p317704
    https://eileenslounge.com/viewtopic.php?p=317857#p317857
    https://eileenslounge.com/viewtopic.php?p=317541#p317541
    https://eileenslounge.com/viewtopic.php?p=317520#p317520
    https://eileenslounge.com/viewtopic.php?p=317510#p317510
    https://eileenslounge.com/viewtopic.php?p=317547#p317547
    https://eileenslounge.com/viewtopic.php?p=317573#p317573
    https://eileenslounge.com/viewtopic.php?p=317574#p317574
    https://eileenslounge.com/viewtopic.php?p=317582#p317582
    https://eileenslounge.com/viewtopic.php?p=317583#p317583
    https://eileenslounge.com/viewtopic.php?p=317605#p317605
    https://eileenslounge.com/viewtopic.php?p=316935#p316935
    https://eileenslounge.com/viewtopic.php?p=317030#p317030
    https://eileenslounge.com/viewtopic.php?p=317030#p317030
    https://eileenslounge.com/viewtopic.php?p=317014#p317014
    https://eileenslounge.com/viewtopic.php?p=316940#p316940
    https://eileenslounge.com/viewtopic.php?p=316927#p316927
    https://eileenslounge.com/viewtopic.php?p=316875#p316875
    https://eileenslounge.com/viewtopic.php?p=316704#p316704
    https://eileenslounge.com/viewtopic.php?p=316412#p316412
    https://eileenslounge.com/viewtopic.php?p=316412#p316412
    https://eileenslounge.com/viewtopic.php?p=316254#p316254
    https://eileenslounge.com/viewtopic.php?p=316046#p316046
    https://eileenslounge.com/viewtopic.php?p=317050&sid=d7e077e50e904a138c794e1 f2115da95#p317050
    https://www.youtube.com/@alanelston2330
    https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-
    https://eileenslounge.com/viewtopic.php?p=316154#p316154
    https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg
    https://teylyn.com/2017/03/21/dollarsigns/#comment-191
    https://eileenslounge.com/viewtopic.php?p=317050#p317050
    https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854
    https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875
    https://eileenslounge.com/viewtopic.php?p=316057#p316057
    https://eileenslounge.com/viewtopic.php?p=315915#p315915
    https://eileenslounge.com/viewtopic.php?p=316705#p316705
    https://eileenslounge.com/viewtopic.php?p=316704#p316704
    https://eileenslounge.com/viewtopic.php?p=176255#p176255
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 07-27-2024 at 01:48 PM.
    xl2007 - Windows 7
    xl hates the 255 number
    Reply With Quote Reply With Quote
  7. 07-28-2011, 11:46 AM #7
    venkat1.sql@gmail.com
    venkat1.sql@gmail.com is offline
    Junior Member
    Join Date
    Jul 2011
    Posts
    2
    Rep Power
    0
    Hi Just Try Below,

    =SUMPRODUCT((A2:A31=A35)*(B2:B31=B35)*(C2:C31))

    Regards,

    Venkat CV
    Reply With Quote Reply With Quote
  8. 07-28-2011, 12:40 PM #8
    Admin
    Admin is offline
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi Venkat,

    Welcome to ExcelFox !

    Your formula gives #VALUE! error.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)
    Reply With Quote Reply With Quote
  9. 07-28-2011, 02:42 PM #9
    venkat1.sql@gmail.com
    venkat1.sql@gmail.com is offline
    Junior Member
    Join Date
    Jul 2011
    Posts
    2
    Rep Power
    0

    Find Value Based On Criteria

    Hi Find attached Work sheet


    sample data Resolved Venkat.xls

    Best Regards,

    Venkat CV
    Reply With Quote Reply With Quote
  10. 07-28-2011, 03:24 PM #10
    Admin
    Admin is offline
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi Venkat,

    Your formula works in the attachment. However, here the OP wants a different solution. Download OP's attachment and test your formula there.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Similar Threads

  1. Summing Data based on certain criteria
    By Howardc in forum Excel Help
    Replies: 7
    Last Post: 01-30-2013, 07:12 PM
  2. Sum values based on multiple criteria
    By Jorrg1 in forum Excel Help
    Replies: 8
    Last Post: 01-07-2013, 03:04 PM
  3. Split data based on criteria
    By Mahesh.sreekakulam in forum Excel Help
    Replies: 3
    Last Post: 06-08-2012, 09:30 PM
  4. Lookup Based on Criteria
    By maruthi in forum Excel Help
    Replies: 2
    Last Post: 02-15-2012, 08:49 AM
  5. Find Value Based On MAX Value
    By Beaker Rex in forum Excel Help
    Replies: 3
    Last Post: 09-13-2011, 06:22 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules