Need help on formula

[purplelily]

I apologise I do not know even how to begin to describe what I need to search for the formula I require (which I have always been doing, thanks to you all out here) and so here it is. I hope someone can help me with this formula.

I have two excel files or sheets:

The first one is where all my data is:
Person/Area Area1 Area2 Area3 Area4
PersonA green blue red yellow
PersonB red blue green yellow
PersonC yellow green red blue
PersonD yellow red blue green

I need a new excel sheet as follows:
Person/Color Red Blue Green Yellow
PersonA Area3 Area2 Area1 Area4
PersonB
PersonC
PersonD

What formula should I have in the second sheet in order to fill in the "Areas"?
Sorry, I don't know to present the above in table but only in text - every word (no space) is one cell.

Many thanks!!
pearlie

 

[WarPigl3t]

Use =HLookUp and follow the directions on the formula prompt.

 

[Marcelo Branco]

Try

Sheet1

	A​	B​	C​	D​	E​
1​	Person/Area​	Area1​	Area2​	Area3​	Area4​
2​	PersonA​	green​	blue​	red​	yellow​
3​	PersonB​	red​	blue​	green​	yellow​
4​	PersonC​	yellow​	green​	red​	blue​
5​	PersonD​	yellow​	red​	blue​	green​

<TBODY>
</TBODY>

Sheet2

	A​	B​	C​	D​	E​
1​	Person/Color​	Red​	Blue​	Green​	Yellow​
2​	PersonA​	Area3​	Area2​	Area1​	Area4​
3​	PersonB​	Area1​	Area2​	Area3​	Area4​
4​	PersonC​	Area3​	Area4​	Area2​	Area1​
5​	PersonD​	Area2​	Area3​	Area4​	Area1​

Formula in B2
=INDEX(Sheet1!$B$1:$E$1,MATCH(B$1,INDEX(Sheet1!$B$2:$E$5,MATCH($A2,Sheet1!$A$2:$A$5,0),0),0))

copy across and down

Hope this helps

M.

 

[purplelily]

Thanks Marcelo! It is perfect.

 

[Marcelo Branco]

Thanks Marcelo! It is perfect.

You are welcome and thanks for the feedback.

M.