IF function to check results of a VLOOKUP and adjust value accordingly

[DarrenHeaphy]

Rather than assume I'm doing this, or rather failing to do this, correctly I'll briefly explain my objective.

I'm managing a project spreadsheet in which one tab is loaded with details about each project, I have another tab which grabs info from the detailed tab but only particular tit bits to form part of an exec report, all of which is fine and working. The challenge I have is that I want to check in the detailed view for a particular value and if it's present display a different text rather than the actual value.

So, for example, check the RAG status of Project A and if it says "Green" then only show G in the exec report. I've assumed I do a VLOOKUP and use an If loop to check for one of the 7 values that can be there.

The 7 values are "Green, Amber, Red, Planning, Cancelled, Closed or Warranty" and I want to show in the exec section instead of that text just "G,A,R,Planning, C,C or Warranty".

I'm completely stuck so any help is appreciated, sample attached.

[NBVC]

If I understand correctly your intent, you can use a Nested Vlookup..

Lookup the value from the Detailed Sheet, and then lookup the result in another sub-table.

See attached.

Formula used:

=VLOOKUP(VLOOKUP(A2,'Detailed View'!$A$2:$B$8,2,0),$I$2:$J$8,2,0)

Where I2:J8 contain the subtable.

[ConneXionLost]

Try:

Please Login or Register  to view this content.

[DarrenHeaphy]

You're both awesome, thank you very much