Lookup within same column

[Craig_320]

Hi, I'm going to try my best to explain this one!
I'm making a list of suppliers that are agents with their own separate sub-suppliers. Column A is my agent name, repeated in each row for different sub-suppliers via that agent. Column B is the sub-supplier name. Column C states 'A' if the agent or 'R' if the sub-supplier. In another column there is a series of calculations that give the Agent (A) a High (H), Medium (M) or Low (L) rating depending on some other criteria. Within that same column is there a way I can get it to show the relevant agents rating score against each of their sub-suppliers?
(I hope that made sense!) I've tried incorporating a vlookup if column C = "R" but that didn't seem to work.
Thanks in advance!

 

[oldbrewer]

agent	sub	Aor R	rating
agenta	sub1	A	H
agenta	sub2	R
agenta	sub3	R
agentb	sub4	A	M
agentb	sub5	R
		in terms of above, what is yourquestion ?

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Craig_320]

Hi

Ratings against 'A' are determined via IF statements based on other columns. in terms of the below I'd like the rating against A to still calculate using these IF statement criteria (sub1 and sub4 results) and sub2 and sub3 to show H, sub5 to show M.

Hope that makes sense!

agent	sub	Aor R	rating
agenta	sub1	A	H
agenta	sub2	R
agenta	sub3	R
agentb	sub4	A	M
agentb	sub5	R
		in terms of above, what is yourquestion ?

<tbody>
</tbody>