Returning value in new sheet based on multiple criteria

[Laura_1993]

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 · Accepted solution]

@Laura_1993 

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

[#3]

This works! Thank you very much!