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

[Crimzo]

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? 

[#2]

@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

[#3 · Accepted 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

[#4]

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!

[#5]

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!

[#6]

@Crimzo 

Hi

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

many thanks

Peter