Help with look up please

[jedibrown]

Hi, so I am having some problems... Again!

Basically, I want to say -

Look in column G, if the value is "pending" return "us", if the value is "In progress" return "them".

The problem is that there are about 12 different potential status' in column G which I want to return either us or them.

So -

Pending - us
in progress - them
client action - us
Info - them

Etc, etc.

Does anyone have any idea how I would do this please?

 

[JazzSP8]

Hi

You could use a VLOOKUP like this;

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">pending</td><td style=";">us</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">pending</td><td style=";">us</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">in progress</td><td style=";">them</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">in progress</td><td style=";">them</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">client action</td><td style=";">us</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">client action</td><td style=";">us</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">info</td><td style=";">them</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">info</td><td style=";">them</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H1</th><td style="text-align:left">=VLOOKUP(<font color="Blue">G1,A1:B12,2,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H2</th><td style="text-align:left">=VLOOKUP(<font color="Blue">G2,A2:B13,2,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H3</th><td style="text-align:left">=VLOOKUP(<font color="Blue">G3,A3:B14,2,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H4</th><td style="text-align:left">=VLOOKUP(<font color="Blue">G4,A4:B15,2,FALSE</font>)</td></tr></tbody></table></td></tr></table><br />

(You didn't specify where you wanted the value returned so I used H)

Does that help?

 

[jedibrown]

Brilliant - I just thought of that, tried it and it works.

Thanks for your help