SOLVED

Getting values from one spreadsheet to another meeting multiple criteria's

%3CLINGO-SUB%20id%3D%22lingo-sub-2143412%22%20slang%3D%22en-US%22%3EGetting%20values%20from%20one%20spreadsheet%20to%20another%20meeting%20multiple%20criteria's%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2143412%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20struggling%20to%20find%20a%20similar%20post%20to%20this%20and%20sorry%20if%20this%20has%20been%20asked%20before.%20I%20am%20wondering%20if%20it%20is%20possible%20to%20tell%20excel%20to%20take%20values%20from%20one%20spreadsheet%20to%20another%20if%20it%20meets%20multiple%20criterias.%20For%20example%2C%20in%20the%20example%20spreadsheet%201%20attached%20I%20would%20like%20to%20take%20data%20for%20values%20from%20Sample%20A%20Reference%201%20at%20size%200.01%20cm%2C%20value%201%20and%20put%20it%20into%20spreadsheet%202%20under%20the%20corresponding%20sample%2C%20reference%20and%20column.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20a%20VLOOKUP%20formula%20work%20with%20a%20match%20and%20countif%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2143412%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2143619%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20values%20from%20one%20spreadsheet%20to%20another%20meeting%20multiple%20criteria's%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2143619%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F854257%22%20target%3D%22_blank%22%3E%40Crimzo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20solution%20using%20vlookup.%20It%20is%20not%20particularly%20elegant%20and%20requires%20a%20'helper'%20column%20(which%20can%20be%20hidden)%20but%20seems%20to%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDepending%20on%20the%20version%20of%20excel%20you%20are%20using%20you%20may%20have%20access%20to%20newer%20functions%20(xlookup)%20which%20may%20not%20need%20a%20helper%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20the%20sheet%202%20need%20to%20be%20in%20this%20horizontal%20format%20-%20which%20is%20different%20from%20the%20vertical%20format%20on%20sheet%201.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20the%20best%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPeter%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2143629%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20values%20from%20one%20spreadsheet%20to%20another%20meeting%20multiple%20criteria's%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2143629%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F854257%22%20target%3D%22_blank%22%3E%40Crimzo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20not%20totally%20clear%20what%20you're%20seeking%20to%20do.%20Is%20it%20the%20return%20of%20a%20value%20meeting%20the%20criteria%2C%20or%20a%20COUNT%20of%20the%20values%20meeting%20the%20criteria%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20a%20formula%20that%20returns%20the%20value%201%2C%20meeting%20the%20criteria%20that%20I%20think%20you%20were%20articulating.%3C%2FP%3E%3CP%3E%3DFILTER('1'!%24D%242%3A%24D%2419%2C('1'!%24A%242%3A%24A%2419%3D'2'!B3)*('1'!B2%3A%24B%2419%3D'2'!C3)*('1'!%24C%242%3A%24C%2419%3D'2'!I%242))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20attached%20the%20revised%20spreadsheet.%26nbsp%3B%20Here%2C%20too%2C%20are%20a%20couple%20of%20useful%20references%20for%20understanding%20the%20FILTER%20function%2C%20which%20is%20what%20I%20used%20to%20meet%20multiple%20criteria%20in%20this%20case.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-filter-function%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-filter-function%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi All,

 

I am struggling to find a similar post to this and sorry if this has been asked before. I am wondering if it is possible to tell excel to take values from one spreadsheet to another if it meets multiple criterias. For example, in the example spreadsheet 1 attached I would like to take data for values from Sample A Reference 1 at size 0.01 cm, value 1 and put it into spreadsheet 2 under the corresponding sample, reference and column. 

 

Would a VLOOKUP formula work with a match and countif? 

5 Replies

@Crimzo 

Hi 

 

I have attached a solution using vlookup. It is not particularly elegant and requires a 'helper' column (which can be hidden) but seems to work.

 

Depending on the version of excel you are using you may have access to newer functions (xlookup) which may not need a helper column.

 

Does the sheet 2 need to be in this horizontal format - which is different from the vertical format on sheet 1.

 

Hope this helps!

 

All the best,

 

Peter

best response confirmed by Crimzo (Occasional Contributor)
Solution

@Crimzo 

 

It's not totally clear what you're seeking to do. Is it the return of a value meeting the criteria, or a COUNT of the values meeting the criteria?

 

Here's a formula that returns the value 1, meeting the criteria that I think you were articulating.

=FILTER('1'!$D$2:$D$19,('1'!$A$2:$A$19='2'!B3)*('1'!B2:$B$19='2'!C3)*('1'!$C$2:$C$19='2'!I$2))

 

I've attached the revised spreadsheet.  Here, too, are a couple of useful references for understanding the FILTER function, which is what I used to meet multiple criteria in this case.

https://exceljet.net/excel-functions/excel-filter-function

https://www.youtube.com/watch?v=9I9DtFOVPIg

 

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, bu...
Hi Peter, Thank you for the response, works well and yes looking to put it into horizontal format but transposing it will take a quite a bit of time I think. Thanks again!
Sorry for my poor description, looking to return a value within the set criteria. Thank you for the response, works well and thank you for the links, will give them a look!

@Crimzo 

Hi

 

If you are happy with the solution can you please mark as complete?

 

many thanks

 

Peter