Conditional formatting/lookup/IF

Hi....first time poster. Hope im in the right section

I have a a worksheet shown below called ADE. There are a number of columns as you can see.

Example 1.jpg

I also have a worksheet called Game by Game shown below

Example 2.jpg

I want to do some conditional formatting - if Vance's name appears in cell B4 of worksheet game by game then I want the number 3 to appear automatically in cell B2 of worksheet ADE.

However, if Vance isn't in B4 of of game by game I want it to look at Cell B5 - if it is there then I want the number 2 to appear in B2 of ADE

Again, if Vance doesn't in Cell B5 then I want it to look in B6, and if Vance is there then I want the number 1 to appear in B2 of ADE.

If Vance is in none of those cells, I then want it to look in cell B8, and if B8 contains Vance then I want B2 of ADE to be shaded green.

Finally, if if Vance is in neither B4, B5, B6 or B8 then cell B2 in ADE can be left blank

I hope this makes sense - I have tried using LOOKUP and IF functions to do this but I am getting tangled up. I only need it for one example and then I can manipulate it for anything else

the spreadsheet itself , rather than images would be helpful

in B2 sheet ADE

If( 'game by game'!B4="vance", 3 , If( 'game by game'!B5="vance", 2, If( 'game by game'!B6="vance", 1, "")))

That should put the numbers into the cells

then in conditional formatting use
='game by game'!B8="vance"
to turn the cell green

see attached

Thanks for the response!

sorry, i don't understand the question

vance is changing cells in the game by game sheet
so not sure exactly what you mean by vance in A2 ?

you can use a lookup in conditional formatting

=NOT(ISERROR(VLOOKUP('game by game'!$B$8,Sheet3!$B$3:$B$5,1,FALSE)))

This is probably not the best way to use it

But it uses vlookup to find a name that matches in sheet three
if it is found then , that is not an error - so returns TRUE
and the NOT - makes it false - so it does not conditional format

isnumber only checks for a number and not a set of names - so any text would be true