Home

Value Search On 3 Criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-643300%22%20slang%3D%22en-US%22%3EValue%20Search%20On%203%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643300%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20morning%20buddies%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20find%20a%20formula%20that%20kind%20search%20in%20multiple%20criteria.%20(see%20attachment%20for%20the%20Excel%20sheet)%3C%2FP%3E%3CP%3EIt%20needs%20to%20search%20in%20the%20Groups%20in%20Sheet%20''1''%2C%20From%20there%20it%20needs%20to%20search%20on%20row%202%2C%20and%20from%20there%20it%20needs%20to%20matching%20criteria%20on%20the%20first%20sheet%20B1.%20So%20when%20I%20change%20the%20Value%20in%20B1%20all%20data%20should%20change.%20Could%20someone%20please%20help%20me%20with%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIlker%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-643300%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-643336%22%20slang%3D%22en-US%22%3ERe%3A%20Value%20Search%20On%203%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643336%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F348050%22%20target%3D%22_blank%22%3E%40ilker_d%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3Ei%20create%20a%20userdefinded%20function.%26nbsp%3BCertainly%20there%20is%20also%20a%20formula%20for%20this%2C%20but%20i%20like%20VBA%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3EFunction%20GetData(strGroup%20As%20String%2C%20strNr%20As%20String)%20As%20Double%3CBR%20%2F%3EDim%20rngFindColumn%20As%20Range%3CBR%20%2F%3EDim%20rngFindRow%20As%20Range%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20rngFindColumn%20%3D%20Tabelle2.Rows(2).Find(what%3A%3DstrGroup%2C%20lookat%3A%3DxlWhole)%3CBR%20%2F%3EIf%20Not%20rngFindColumn%20Is%20Nothing%20Then%3CBR%20%2F%3ESet%20rngFindRow%20%3D%20Tabelle2.Range(%22A%3AA%22).Find(what%3A%3DstrNr%2C%20lookat%3A%3DxlWhole)%3CBR%20%2F%3EIf%20Not%20rngFindRow%20Is%20Nothing%20Then%3CBR%20%2F%3EGetData%20%3D%20Tabelle2.Cells(rngFindRow.Row%2C%20rngFindColumn.Column).Value%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Function%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecall%20from%20cell%20B3%3A%26nbsp%3B%3DGetData(%24B%241%3B%24A3)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3EBernd%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fvba-tanker.com%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fvba-tanker.com%2F%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-643917%22%20slang%3D%22en-US%22%3ERe%3A%20Value%20Search%20On%203%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643917%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347933%22%20target%3D%22_blank%22%3E%40Berndvbatanker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20reply%20but%20I%20don't%20know%20what%20a%20VBA%20means.%3C%2FP%3E%3CP%3EIsn't%20there%20just%20a%20formula%20that%20I%20can%20add%20into%20cell%20B3%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644036%22%20slang%3D%22en-US%22%3ERe%3A%20Value%20Search%20On%203%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644036%22%20slang%3D%22en-US%22%3EPerhaps%20you%20need%20to%20manually%20enter%20your%20desired%20results%2C%20then%20attach%20your%20file%20again%20so%20I%20can%20understand%20the%20logic%20of%20the%20formula%20you%20need.%20Formula%2C%20not%20VBA%2C%20should%20be%20the%20default%20solution.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644047%22%20slang%3D%22en-US%22%3ERe%3A%20Value%20Search%20On%203%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644047%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Twifoo%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20filled%20in%20the%20data.%20So%20If%20you%20change%20Cell%20B1%20(for%20example%200155)%20all%20data%20should%20change%20with%20it.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644398%22%20slang%3D%22en-US%22%3ERe%3A%20Value%20Search%20On%203%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644398%22%20slang%3D%22en-US%22%3EYou%20can%20try%20this%20formula%20in%20B3%2C%20copied%20down%20rows%3A%3CBR%20%2F%3E%3DIFNA(VLOOKUP(A3%2C1!A%243%3AE%2410%2C%3CBR%20%2F%3EMATCH(B%241%2C1!A%242%3AE%242%2C0)%2C0)%2C0)%3C%2FLINGO-BODY%3E
ilker_d
Occasional Contributor

Good morning buddies,

 

I'm trying to find a formula that kind search in multiple criteria. (see attachment for the Excel sheet)

It needs to search in the Groups in Sheet ''1'', From there it needs to search on row 2, and from there it needs to matching criteria on the first sheet B1. So when I change the Value in B1 all data should change. Could someone please help me with this?

 

Thanks in advance

 

Best regards,

 

Ilker 

5 Replies

@ilker_d 

Hi,

i create a userdefinded function. Certainly there is also a formula for this, but i like VBA

 

Function GetData(strGroup As String, strNr As String) As Double
Dim rngFindColumn As Range
Dim rngFindRow As Range

Set rngFindColumn = Tabelle2.Rows(2).Find(what:=strGroup, lookat:=xlWhole)
If Not rngFindColumn Is Nothing Then
Set rngFindRow = Tabelle2.Range("A:A").Find(what:=strNr, lookat:=xlWhole)
If Not rngFindRow Is Nothing Then
GetData = Tabelle2.Cells(rngFindRow.Row, rngFindColumn.Column).Value
End If
End If

End Function

 

call from cell B3: =GetData($B$1;$A3)

 

Regards

Bernd

https://vba-tanker.com/

@Berndvbatanker 

Thank you for your reply but I don't know what a VBA means.

Isn't there just a formula that I can add into cell B3?

Perhaps you need to manually enter your desired results, then attach your file again so I can understand the logic of the formula you need. Formula, not VBA, should be the default solution.

@Twifoo 

 

Hi Twifoo,

 

I have filled in the data. So If you change Cell B1 (for example 0155) all data should change with it. 

You can try this formula in B3, copied down rows:
=IFNA(VLOOKUP(A3,1!A$3:E$10,
MATCH(B$1,1!A$2:E$2,0),0),0)
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
12 Replies