Index match with conditional formating

[SAXON10]

Hi,

Good evening Excel Legends,

I am trying to apply conditional formatting to a column in Excel using the "Use a formula to determine which cells to format" feature.

In DATA sheet have a list of names in column A with corresponding reference numbers in column B and type in column C. In DATA sheet I have a longer list of reference numbers in row G4. I want the conditional formatting rule to look up the reference numbers in DATA Sheet against the row G4 and highlight the cell conditional formatting

File attached for your reference. Please assist me.

https://www.dropbox.com/s/78v00v0lbpcgpxk/ME.xlsx?dl=0

DATA;

[TABLE="width: 321"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD]SUBJECT[/TD]
[TD]ID NO[/TD]
[TD]TYPE[/TD]
[TD="colspan: 2"]COLOR CODE[/TD]
[/TR]
[TR]
[TD]AA1[/TD]
[TD]3320[/TD]
[TD]MATHS[/TD]
[TD] [/TD]
[TD]HIS[/TD]
[/TR]
[TR]
[TD]AA2[/TD]
[TD]3321[/TD]
[TD]MATHS[/TD]
[TD] [/TD]
[TD]MATHS[/TD]
[/TR]
[TR]
[TD]AA3[/TD]
[TD]7320[/TD]
[TD]MATHS[/TD]
[TD] [/TD]
[TD]SCI[/TD]
[/TR]
[TR]
[TD]AA4[/TD]
[TD]7520[/TD]
[TD]MATHS[/TD]
[TD] [/TD]
[TD]ENG[/TD]
[/TR]
[TR]
[TD]AA5[/TD]
[TD]7521[/TD]
[TD]MATHS[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA6[/TD]
[TD]WAIT[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA7[/TD]
[TD]9900[/TD]
[TD]MATHS[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA8[/TD]
[TD]6500[/TD]
[TD]MATHS[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA9[/TD]
[TD]6586[/TD]
[TD]ENG[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA10[/TD]
[TD]6530[/TD]
[TD]HIS[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA11[/TD]
[TD]8600[/TD]
[TD]MATHS[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA12[/TD]
[TD]5321[/TD]
[TD]MATHS[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA13[/TD]
[TD]WAIT[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA14[/TD]
[TD]2255[/TD]
[TD]SCI[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA15[/TD]
[TD]8581[/TD]
[TD]SCI[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA16[/TD]
[TD]8580[/TD]
[TD]MATHS[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA17[/TD]
[TD]5320[/TD]
[TD]MATHS[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA18[/TD]
[TD]5325[/TD]
[TD]SCI[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA19[/TD]
[TD]1100[/TD]
[TD]SCI[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA20[/TD]
[TD]2233[/TD]
[TD]MATHS[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA21[/TD]
[TD]2230[/TD]
[TD]HIS[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA22[/TD]
[TD]9500[/TD]
[TD]MATHS[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA23[/TD]
[TD]9531[/TD]
[TD]MATHS[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA24[/TD]
[TD]9680[/TD]
[TD]MATHS[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA25[/TD]
[TD]9681[/TD]
[TD]SCI[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA26[/TD]
[TD]3500[/TD]
[TD]ENG[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA27[/TD]
[TD]3501[/TD]
[TD]ENG[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA28[/TD]
[TD]4500[/TD]
[TD]MATHS[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA29[/TD]
[TD]4501[/TD]
[TD]SCI[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA30[/TD]
[TD]4502[/TD]
[TD]ENG[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA31[/TD]
[TD]4503[/TD]
[TD]ENG[/TD]
[TD][/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]

DESIRED RESULT

[TABLE="width: 1331"]
<colgroup><col><col span="5"><col><col span="3"><col span="3"><col><col span="18"></colgroup><tbody>[TR]
[TD]SUBJECT[/TD]
[TD]AA1[/TD]
[TD]AA2[/TD]
[TD]AA3[/TD]
[TD]AA4[/TD]
[TD]AA5[/TD]
[TD]AA6[/TD]
[TD]AA7[/TD]
[TD]AA8[/TD]
[TD]AA9[/TD]
[TD]AA10[/TD]
[TD]AA11[/TD]
[TD]AA12[/TD]
[TD]AA13[/TD]
[TD]AA14[/TD]
[TD]AA15[/TD]
[TD]AA16[/TD]
[TD]AA17[/TD]
[TD]AA18[/TD]
[TD]AA19[/TD]
[TD]AA20[/TD]
[TD]AA21[/TD]
[TD]AA22[/TD]
[TD]AA23[/TD]
[TD]AA24[/TD]
[TD]AA25[/TD]
[TD]AA26[/TD]
[TD]AA27[/TD]
[TD]AA28[/TD]
[TD]AA29[/TD]
[TD]AA30[/TD]
[TD]AA31[/TD]
[/TR]
[TR]
[TD]ID NO[/TD]
[TD]3320[/TD]
[TD]3321[/TD]
[TD]7320[/TD]
[TD]7520[/TD]
[TD]7521[/TD]
[TD]WAIT[/TD]
[TD]9900[/TD]
[TD]6500[/TD]
[TD]6586[/TD]
[TD]6530[/TD]
[TD]8600[/TD]
[TD]5321[/TD]
[TD]WAIT[/TD]
[TD]2255[/TD]
[TD]8581[/TD]
[TD]8580[/TD]
[TD]5320[/TD]
[TD]5325[/TD]
[TD]1100[/TD]
[TD]2233[/TD]
[TD]2230[/TD]
[TD]9500[/TD]
[TD]9531[/TD]
[TD]9680[/TD]
[TD]9681[/TD]
[TD]3500[/TD]
[TD]3501[/TD]
[TD]4500[/TD]
[TD]4501[/TD]
[TD]4502[/TD]
[TD]4503[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

I think you can
Select B2:B32
Ctrl+C
Select H4
Paste Special; All, and check Transpose

M.

 

[SAXON10]

thank for your reply. There is no another way. I have a more data. Its more hard to apply transpose every time. Please help me.

 

[Marcelo Branco]

I think you should
Turn-on the macro recorder
Do the copy + paste/transpose manually
Turn-off the macro recorder
So you have a code to automate the process

M.

 

[SAXON10]

I think you should
Turn-on the macro recorder
Do the copy + paste/transpose manually
Turn-off the macro recorder
So you have a code to automate the process

M.

thanks for your response and reply. I followup your suggestion.