Feb 17 2021 08:27 AM
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?
Feb 17 2021 09:04 AM
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
Feb 17 2021 09:08 AM
Solution
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
Feb 18 2021 12:08 AM
Feb 18 2021 12:09 AM
Feb 18 2021 03:55 AM
Feb 17 2021 09:08 AM
Solution
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