Find Value Based On Criteria

[excel_learner]

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.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.eileenslounge.com/viewtopic.php?p=296754#p296754 (https://www.eileenslounge.com/viewtopic.php?p=296754#p296754)
https://www.eileenslounge.com/viewtopic.php?p=296859#p296859 (https://www.eileenslounge.com/viewtopic.php?p=296859#p296859)
https://teylyn.com/2017/03/21/dollarsigns/#comment-191 (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/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.excelfox.com/forum/showthread.php/2355-Tests-and-Notes-on-Range-Referrencing/page8)
https://www.eileenslounge.com/viewtopic.php?p=296859#p296859 (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/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.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 (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Admin]

Hi,

Try

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

[pjotrsk]

Hi,

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

This is user-defined formula:


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

[Admin]

Hi pjotrsk,

Welcome to ExcelFox !

Please use code tags while posting codes :)

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

[Haseeb A]

Hello, Also try this,


=INDEX(D:D,MATCH("*"&J2&"*",A:A,0))

[Rasm]

If you want a string comparison to be insensetive to case then use

Option Compare Text ' in the declaration section of your program

[venkat1.sql@gmail.com]

Hi Just Try Below,

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

Regards,

Venkat CV

[Admin]

Hi Venkat,

Welcome to ExcelFox !

Your formula gives #VALUE! error.

[venkat1.sql@gmail.com]

Hi Find attached Work sheet


74

Best Regards,

Venkat CV

[Admin]

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.

[excel_learner]

Thanks all for the assistance...