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]

[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]agent[/TD]
[TD="width: 64"]sub[/TD]
[TD="width: 64"]Aor R[/TD]
[TD="width: 64"]rating[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]agenta[/TD]
[TD]sub1[/TD]
[TD]A[/TD]
[TD]H[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]agenta[/TD]
[TD]sub2[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]agenta[/TD]
[TD]sub3[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]agentb[/TD]
[TD]sub4[/TD]
[TD]A[/TD]
[TD]M[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]agentb[/TD]
[TD]sub5[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]in terms of above, what is yourquestion ?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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!

[TABLE="width: 448"]
<tbody>[TR]
[TD="width: 64"]agent[/TD]
[TD="width: 64"]sub[/TD]
[TD="width: 64"]Aor R[/TD]
[TD="width: 64"]rating[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]agenta[/TD]
[TD]sub1[/TD]
[TD]A[/TD]
[TD]H[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]agenta[/TD]
[TD]sub2[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]agenta[/TD]
[TD]sub3[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]agentb[/TD]
[TD]sub4[/TD]
[TD]A[/TD]
[TD]M[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]agentb[/TD]
[TD]sub5[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]in terms of above, what is yourquestion ?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]