return a value that satisfying two conditions

%3CLINGO-SUB%20id%3D%22lingo-sub-2609208%22%20slang%3D%22en-US%22%3Ereturn%20a%20value%20that%20satisfying%20two%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2609208%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20a%20beginner%20at%20Excel%2C%20what%20i%20am%20trying%20to%20do%20is%20%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Etable%26nbsp%3B%20A%20returns%20two%20conditions%20that%20table%26nbsp%3B%20B%20should%20take%20in%20consideration%20to%20send%20me%20the%20good%20value%20that%20i%20will%20use%20in%20a%20certain%20cell%26nbsp%3B%20in%20my%20table%20A%20%2C%20the%20hard%20thing%20is%20the%20table%26nbsp%3B%20A%20has%20an%26nbsp%3B%20unlimated%20columns%20and%20rows%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eis%20there%26nbsp%3B%20any%20functions%20that%20i%20can%26nbsp%3B%20work%20with%3F%26nbsp%3B%20i%20know%20there%20is%20recherchev()%20but%20i%20can't%20see%20how%20to%20use%20it%20in%20this%20prob%20especially%20that%20my%20wanted%20value%20has%20to%20satisfy%20two%20conditions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2609208%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2609442%22%20slang%3D%22en-US%22%3ERe%3A%20return%20a%20value%20that%20satisfying%20two%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2609442%22%20slang%3D%22en-US%22%3EHi%2C%20Do%20you%20mean%20you%20want%20to%20use%20the%20VLOOKUP%20Function%20for%20multiple%20conditions%3F%20Please%20provide%20the%20Excel%20file%20to%20understand%20and%20solve%20your%20problem%20better.%20Thank%20you%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2609518%22%20slang%3D%22en-US%22%3ERe%3A%20return%20a%20value%20that%20satisfying%20two%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2609518%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1118424%22%20target%3D%22_blank%22%3E%40nidhiachhaa9%3C%2FA%3E%26nbsp%3B%20thank%20you%20for%20your%20help%20so%20my%20table%20A%20is%20like%20that%20(with%20many%20rows)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Rach_1990_1-1628072240269.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F300411iB38F35ED2E8156F1%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Rach_1990_1-1628072240269.png%22%20alt%3D%22Rach_1990_1-1628072240269.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Efor%20example%20to%20get%201%20as%20result%20in%20table%20A%20i%20had%20to%20search%20the%20value%20according%20to%20(6MV%20%26amp%3B%2020%C2%B0)%20in%20table%20B%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Rach_1990_2-1628072413198.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F300412i9C313FFE248C6527%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Rach_1990_2-1628072413198.png%22%20alt%3D%22Rach_1990_2-1628072413198.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eso%20i%20want%20to%20make%26nbsp%3B%20the%20action%20of%20getting%20the%20right%20data%20automatic%20but%20i%20dont%20know%20how%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20alot%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2609977%22%20slang%3D%22en-US%22%3ERe%3A%20return%20a%20value%20that%20satisfying%20two%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2609977%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1120100%22%20target%3D%22_blank%22%3E%40Rach_1990%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22user-login%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1118424%22%20target%3D%22_blank%22%3Enidhiachhaa9%3C%2FA%3E%26nbsp%3Basked%20if%20you%20could%20supply%20the%20actual%20Excel%20file.%20%3CU%3E%3CSTRONG%3EImages%20are%20not%20at%20all%20as%20helpful%20as%20the%20actual%20file.%3C%2FSTRONG%3E%3C%2FU%3E%20But%20seeing%20the%20actual%20spreadsheet%2C%20the%20source%20data%20and%20the%20desired%20result...what%20you've%20tried%20so%20far...%3C%2FDIV%3E%3CDIV%20class%3D%22user-login%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22user-login%22%3EIf%20you%20have%20to%20create%20a%20modified%20version%20(or%20a%20mock-up)%20to%20conceal%20confidential%20information%2C%20please%20do%20that.%20But%20please%20give%20us%20an%20actual%20Excel%20file....%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2611199%22%20slang%3D%22en-US%22%3ERe%3A%20return%20a%20value%20that%20satisfying%20two%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2611199%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3Ethank%20you%20for%20your%20responds%2C%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1118424%22%20target%3D%22_blank%22%3E%40nidhiachhaa9%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethis%20a%20template%20file%20of%20what%20i%20am%20doing.%20the%20purpose%20is%20to%20find%20how%20i%20can%20get%20the%20right%20value%20from%20the%26nbsp%3B%20table%20B%26nbsp%3B%20and%20put%20it%20in%20the%20table%20A.%20as%20you%20can%20see%20at%20the%20moment%20it%20is%20easy%20but%20if%20i%20started%20to%20creat%20many%20rows%20with%20different%20energy%20and%20angle%20%2C%20i%20will%20need%20a%20fuch%20to%20make%20it%20automatic%20and%20eiser%20for%20the%20user%20of%20my%20futur%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2611954%22%20slang%3D%22en-US%22%3ERe%3A%20return%20a%20value%20that%20satisfying%20two%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2611954%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1120100%22%20target%3D%22_blank%22%3E%40Rach_1990%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMay%20I%20offer%20some%20feedback%3F%20Whoever%20created%20this%20spreadsheet--you%20or%20somebody%20else--spent%20far%20too%20much%20time%20on%20making%20it%20look%20pretty.%20I%20have%20nothing%20against%20beauty--the%20use%20of%20colors%20and%20formatting%20to%20make%20things%20stand%20out--but%20it%20makes%20for%20far%20greater%20usefulness%20if%20one%20FIRST%20concentrates%20on%20getting%20the%20functionality%20that%20is%20needed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20your%20case%2C%20for%20example%2C%20that%20range%20of%20cells%20from%20I9%20to%20I12%20is%20made%20to%20look%20as%20if%20it's%20a%20single%20cell%2C%20but%20it%20isn't.%20It's%20four%20separate%20cells%20that%20have%20been%20merged%20into%20one.%20Great%20for%20looks%3B%20but%20it%20interferes%20with%20functionality.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20I%20therefore%20suggest%20you%20step%20back%20and%20create%20a%20simpler%20layout%20of%20what%20you%20really%20need--we%20can%20make%20it%20prettier%20later--with%20rows%20of%20single%20cells%20and%20single%20columns.%20It's%20also%20not%20altogether%20clear%20what%20the%20relationship%20is%20between%20the%20parameters%20sheet%20and%20tables%20A%20and%20B...%20%26nbsp%3BAnd%20please%2C%20for%20the%20sake%20of%20testing%2C%20include%20not%20just%20a%20basic%20template%20with%20one%20or%20two%20sets%20of%20data%3B%20add%20more%20representative%20data%20so%20we%20can%20work%20with%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20you're%20wanting%20to%20do%20is%2C%20I%20am%20confident%2C%20relatively%20easy%20with%20Excel's%20many%20ways%20to%20parse%20tables%20of%20data%2C%20but%20we%20need%20to%20work%20with%20well-defined%20tables%20at%20the%20start.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2613820%22%20slang%3D%22en-US%22%3ERe%3A%20return%20a%20value%20that%20satisfying%20two%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2613820%22%20slang%3D%22en-US%22%3EThank%20you%20alot%20for%20your%20feedback%20%2C%20the%20fact%20is%20%2C%20at%20the%20begining%20of%20my%20project%20i%20had%20a%20cetain%20look%20to%20give%20back%20so%20it%20is%20not%20really%20a%20choice%20however%20it%20is%20not%20a%20prob%20because%20i%20can%20use%20only%20one%20of%20these%20cells%20and%20results%20would%20still%20correct.%3CBR%20%2F%3Ei%20tried%20to%20send%20this%20template%20to%20give%20only%20an%20idea%20of%20what%20i%20am%20doing%20but%20in%20fact%20the%20file%20excel%20is%20much%20bigger%20then%20this%20and%20so%20far%20i%20reached%20the%20goal%20of%20the%20project%2C%20the%20second%20step%20now%20is%20to%20make%20my%20file%20easy%20use%20and%20that%20by%20automatizing%20the%20choice%20of%20the%20right%20data%20in%20table%20B%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2615021%22%20slang%3D%22en-US%22%3ERe%3A%20return%20a%20value%20that%20satisfying%20two%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2615021%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1120100%22%20target%3D%22_blank%22%3E%40Rach_1990%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20I%20said%2C%20I'm%20not%20opposed%20to%20making%20a%20spreadsheet%20look%20nice.%20But%20work%20on%20that%20AFTER%20you've%20gotten%20the%20functionality%20you%20need.%20In%20sales%2C%20people%20often%20talk%20about%20the%20sizzle%20or%20the%20steak%2C%20with%20the%20cynical%20point%20%22If%20you%20don't%20have%20a%20good%20steak%2C%20then%20sell%20the%20sizzle.%22%20In%20spreadsheet%20design%2C%20all%20too%20often%2C%20I%20see%20people%20devote%20far%20too%20much%20attention%20to%20the%20sizzle%20before%20getting%20the%20steak.%20By%20all%20means%2C%20make%20it%20pretty%3B%20first%20make%20it%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20that%20in%20mind%2C%20please%20send%20the%20whole%20file%2C%20the%20bigger%20one...or%20at%20least%20a%20more%20representative%20one.%20A%20template%20does%20just%20what%20you've%20said--%22give%20an%20idea%22--but%20not%20much%20more.%20You%20want%20to%20have%20the%20bigger%20project%20more%20automated%2C%20and%20the%20template%20%3CEM%3Eper%20se%3C%2FEM%3E%20puts%20emphasis%20on%20appearance%20over%20functionality.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20please%20send%20a%20more%20complete%20file%2C%20with%20a%20more%20complete%20definition%20of%20how%20the%20parameters%20(or%20criteria)%20affect%20the%20selection%20of%20the%20data%20to%20appear.%20As%20has%20already%20been%20said%20in%20this%20thread%2C%20there%20are%20various%20ways%20within%20Excel%20to%20retrieve%20data%20based%20on%20criteria.%20VLOOKUP%20is%20the%20most%20basic.%20More%20recently%20there%20are%20functions%20like%20XLOOKUP%20and%20FILTER%20that%20are%20even%20more%20powerful.%20Here's%20a%20good%20resource%20if%20you%20want%20to%20research%20those%20on%20your%20own.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-xlookup-function%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-xlookup-function%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello!

 

I am a beginner at Excel, what i am trying to do is : 

 

table  A returns two conditions that table  B should take in consideration to send me the good value that i will use in a certain cell  in my table A , the hard thing is the table  A has an  unlimated columns and rows,

 

is there  any functions that i can  work with?  i know there is recherchev() but i can't see how to use it in this prob especially that my wanted value has to satisfy two conditions.

 

 

Thank you for your help

13 Replies
Hi, Do you mean you want to use the VLOOKUP Function for multiple conditions? Please provide the Excel file to understand and solve your problem better. Thank you

@nidhiachhaa9  thank you for your help so my table A is like that (with many rows)

Rach_1990_1-1628072240269.png

for example to get 1 as result in table A i had to search the value according to (6MV & 20°) in table B 

Rach_1990_2-1628072413198.png

 

so i want to make  the action of getting the right data automatic but i dont know how? 

 

Thanks alot

 

@Rach_1990 

 

@mathetesthank you for your responds,

@nidhiachhaa9 

 

this a template file of what i am doing. the purpose is to find how i can get the right value from the  table B  and put it in the table A. as you can see at the moment it is easy but if i started to creat many rows with different energy and angle , i will need a fuch to make it automatic and eiser for the user of my futur file.

 

Thank you for your help.

@Rach_1990 

 

May I offer some feedback? Whoever created this spreadsheet--you or somebody else--spent far too much time on making it look pretty. I have nothing against beauty--the use of colors and formatting to make things stand out--but it makes for far greater usefulness if one FIRST concentrates on getting the functionality that is needed.

 

In your case, for example, that range of cells from I9 to I12 is made to look as if it's a single cell, but it isn't. It's four separate cells that have been merged into one. Great for looks; but it interferes with functionality.

 

Could I therefore suggest you step back and create a simpler layout of what you really need--we can make it prettier later--with rows of single cells and single columns. It's also not altogether clear what the relationship is between the parameters sheet and tables A and B...  And please, for the sake of testing, include not just a basic template with one or two sets of data; add more representative data so we can work with it.

 

What you're wanting to do is, I am confident, relatively easy with Excel's many ways to parse tables of data, but we need to work with well-defined tables at the start.

Thank you alot for your feedback , the fact is , at the begining of my project i had a cetain look to give back so it is not really a choice however it is not a prob because i can use only one of these cells and results would still correct.
i tried to send this template to give only an idea of what i am doing but in fact the file excel is much bigger then this and so far i reached the goal of the project, the second step now is to make my file easy use and that by automatizing the choice of the right data in table B

@Rach_1990 

 

As I said, I'm not opposed to making a spreadsheet look nice. But work on that AFTER you've gotten the functionality you need. In sales, people often talk about the sizzle or the steak, with the cynical point "If you don't have a good steak, then sell the sizzle." In spreadsheet design, all too often, I see people devote far too much attention to the sizzle before getting the steak. By all means, make it pretty; first make it work.

 

With that in mind, please send the whole file, the bigger one...or at least a more representative one. A template does just what you've said--"give an idea"--but not much more. You want to have the bigger project more automated, and the template per se puts emphasis on appearance over functionality.

 

So please send a more complete file, with a more complete definition of how the parameters (or criteria) affect the selection of the data to appear. As has already been said in this thread, there are various ways within Excel to retrieve data based on criteria. VLOOKUP is the most basic. More recently there are functions like XLOOKUP and FILTER that are even more powerful. Here's a good resource if you want to research those on your own. https://exceljet.net/excel-functions/excel-xlookup-function

 

@mathetes 

I appreciate your advices , i completely understand your point of view, it is my first experience with Excel and the purpuse was to understand some mathematicals problrms which are related to the medical field , thats why i spent much time to understand that and give solutions so at this point i am doing well with and the next step is to present correctly my work in Excel.

here bellow you will find another template with much details.

Again i really appreciat your help and i hope it is going to be clear now.

 

 

@Rach_1990 

 

What isn't clear to me is how all the different sheets relate to one another. Nor is it clear what you're still working to resolve. You clearly have some functioning formulas in there--because you're working in French, a language I don't understand--I'm limited to begin with in trying to decipher what's going on.

 

So for me--and I suspect for others--to be able to help, this workbook is a good start (Thank you!) but we would need a much clearer description of what specific elements of information you are seeking to have retrieved, from what other part of the workbook, based on what criteria (and where those criteria are to be found).

i am sorry i am a french speaker and thats explain my bad english
So the work is about determining the thickness of a radiotherapy room.
so the workbook Table 1 , tabla 2 Table 3 are not related to each other , all of them are related to the first notework where i do have my parameters which is automated and also Table 1, table
2 Table 3 are related to the workbook DataBase and this what i am trying to automate right now .
In the notework DataBase i wrote some remarks of what i am trying to do

@Rach_1990 

i am sorry i am a french speaker and thats explain my bad english

Truly, your English is not a problem at all. 


So the work is about determining the thickness of a radiotherapy room.
so the workbook Table 1 , tabla 2 Table 3 are not related to each other , all of them are related to the first notework where i do have my parameters which is automated and also Table 1, table
2 Table 3 are related to the workbook DataBase and this what i am trying to automate right now .
In the notework DataBase i wrote some remarks of what i am trying to do

 

I see those brief remarks, but don't fully comprehend them (that is, I'm not able to take what you've written and think about them in the logical terms that are needed to come up with the formulas). Also, you have those columns in Table A3 and A2 filled in...so what is it that you're missing?

Yes those columns are filed in by hand because i am the one who created the Excel so i know how to get the right data , but imagine for a certain user the situation it would be hard for him so what i am willing to do is :
let the user choose the angle and the enegy than the DataBse send the right value to the right column .
I undestand that it is hard for you to help me because you dont fully understan what i am hoping to do .
Anyway thank you alot you really tried to helped me and i appreciate that.

@Rach_1990 

 

those columns are filed in by hand because i am the one who created the Excel so i know how to get the right data , but imagine for a certain user the situation it would be hard for him so what i am willing to do is :
let the user choose the angle and the enegy than the DataBse send the right value to the right column .

 

So what you would need to do for "a certain user" is to explain in detail (1) where that user would find the angle and energy values, and then (2) how those values would be used to retrieve (look up) and then (3) "send the right value to the right column".....that's all I'm asking for too. Another way to say it, you know how to navigate around your tables; you need to take me (or that hypothetical new user) by the hand and show us both what you are taking for granted.