SOLVED

# Returning value in new sheet based on multiple criteria

Copper Contributor

# Returning value in new sheet based on multiple criteria

Hello!

I am trying to rearrange my Excel dataset (+/- 5000 patients). I'm hoping someone can help me with the following.

As an example, currently the dataset is arranged like this on Sheet 1:

The data is stored in long format and I want to reshape it to wide, based on certain criteria. I thought I should place the rearranged dataset on Sheet 2. The new format should look like this:

A measurement for 'FWD' returns in sheet 2 (for example, FWD_1) based on three criteria:

- Measurementnr = 1

- On time = 1

- Matching patient number (the unique patient numbers are already in Column A in Sheet 2)

The same should be done for FWD_2 (= measurementnr 2) and FWD_3 (=measurementnr 3). If a measurement was not performed on time, the value should not return in Sheet 2.

Any help would be much appreciated!

2 Replies
best response confirmed by Laura_1993 (Copper Contributor)
Solution

# Re: Returning value in new sheet based on multiple criteria

``=IFERROR(INDEX(\$F\$2:\$F\$13,MATCH(1,(\$A\$2:\$A\$13=\$H2)*(\$C\$2:\$C\$13=I\$1)*(\$E\$2:\$E\$13=1),0)),"-")``

Maybe with this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

# Re: Returning value in new sheet based on multiple criteria

This works! Thank you very much!
1 best response

Accepted Solutions
best response confirmed by Laura_1993 (Copper Contributor)
Solution

# Re: Returning value in new sheet based on multiple criteria

``=IFERROR(INDEX(\$F\$2:\$F\$13,MATCH(1,(\$A\$2:\$A\$13=\$H2)*(\$C\$2:\$C\$13=I\$1)*(\$E\$2:\$E\$13=1),0)),"-")``

Maybe with this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.