Find Value Based On Criteria

**excel_learner** · 07-27-2011, 01:27 PM

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

**Admin** · 07-27-2011, 02:15 PM

Hi,

Try

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

**pjotrsk** · 07-27-2011, 05:46 PM

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

**Admin** · 07-27-2011, 08:06 PM

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** · 07-27-2011, 11:24 PM

Hello, Also try this,

Code:

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

**Rasm** · 07-28-2011, 04:52 AM

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** · 07-28-2011, 11:46 AM

Hi Just Try Below,

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

Regards,

Venkat CV

**Admin** · 07-28-2011, 12:40 PM

Hi Venkat,

Welcome to ExcelFox !

Your formula gives #VALUE! error.

**venkat1.sql@gmail.com** · 07-28-2011, 02:42 PM

Hi Find attached Work sheet

sample data Resolved Venkat.xls

Best Regards,

Venkat CV

**Admin** · 07-28-2011, 03:24 PM

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.