Formula to adjust data from 1 table to another

[Jeddies]

Hi Everyone,

I'm attempting to move the data in the first table for it to be formatted by formulas into the below table.

The first tables data can have all different fruits with approvals in any column and Im looking to have it correctly spaced out under its own column by checking against the text value or the different fruit's.

I've been attempting to index it with match but that only seems to look at the top row and the first column of the first table.

I need it to look at the first column for the name and and look in that particular row for the specific fruit and then produce the value. In the second table.

Can use multiple formulas to get there if it's easier.

Hope that makes sense.

Any help would be greatly appreciated. Thank you in advance.

 

[Peter Bartholomew]

I need to know what version of Excel you use (if any of my solutions work on versions prior to 365, it is largely by chance).

= LET(
      statusRow, XLOOKUP(@Name, NameInput, StatusTable),
      XLOOKUP(Fruit&"*", statusRow,statusRow, "Not requested",2)
   )

where 'Name' is given by

= SORT(NameInput)

 

[p45cal]

This is work in progress so all you will have to do is right-click the green table in the attached and choose Refresh.
I've added headers to your 'From This' table but I should be able to do without them (although it would be safer to have at least headers for Name and Unit then assume anything else is an 'approval' column. That way you aren't restricted to just 3 'approval' columns in the future. Is that likely?
I've also tweaked the results so that instead of showing 'Tomato-Approved' it just shows 'Approved' since all the results in that column will pertain to Tomato in the header; is that OK?
This uses Power Query (aka Get & Transform Data). Does your version of Excel allow you to right-click the green table and chose Refresh?
If you could answer the 3 Qs in red please.

 

[Jeddies]

This is work in progress so all you will have to do is right-click the green table in the attached and choose Refresh.
I've added headers to your 'From This' table but I should be able to do without them (although it would be safer to have at least headers for Name and Unit then assume anything else is an 'approval' column. That way you aren't restricted to just 3 'approval' columns in the future. Is that likely?
I've also tweaked the results so that instead of showing 'Tomato-Approved' it just shows 'Approved' since all the results in that column will pertain to Tomato in the header; is that OK?
This uses Power Query (aka Get & Transform Data). Does your version of Excel allow you to right-click the green table and chose Refresh?
If you could answer the 3 Qs in red please.

Hi,

Thank you for your help that was exactly what I needed. Much appreciated.