Extract values (row values and column values) with formula

Have raw data in A4:F11, and looking for a formula to extract values in Row D, E, F,......if the condition I select from the drop down option (A14) is met.

For example, if A14 = Acura, would like the formula to extract the values in Row D4, E4 and F4 and transpose it into a column value (A17, A18, A19, and so on. The formula should also extract column values in B5:B11 and transpose the values across B16:D16 if dropdown criteria is met.

See attached sample file. Outcome is GREEN color


Thanks

I think the way you are trying to extract values is wrong. You have shown the desired output for Acura, that's ok, it can be extracted as shown in the example but how do you expect the desired output for All [Acura,Lexus]. Would you upload the workbook showing the desired output for All [Acura,Lexus]?

Try this...

In B17

Please Login or Register  to view this content.

and then copy across and down.

Is this what you are trying to achieve?

Something like this?

Like this? There are two different ways of handling the All option shown.

Are you not getting the output as same as you showed in your sample sheet as desired output? I don't understand that what you are asking for?

Are these values expected to change much? Are they very different in your real application from what is shown in your sample workbook?
The reason that I'm asking is that the formula for figuring out all the unique values (OK, Good, Fair, Excellent) is a bit complex and it brings up the values in order of appearance rather than in the order of their perceived value.

With an pivot table.

See the attached file.

I used the file of Jacc for that (thanks for that).

Please find the attached sheet to see if this is what you are trying to achieve?

What about the pivot table?

See the attached file.

Please see attached file with self-adjusting ratings

Did this work for you?