SOLVED

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

Occasional Contributor

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

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

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

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

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

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

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...

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

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!

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

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!

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

Hi

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

many thanks

Peter