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
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies