If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Formula to return a value in column B conditional upon the value in Column A
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Formula to return a value in column B conditional upon the value in Column A

#1
June 1st 12, 09:18 PM
 JackofAllTrades Junior Member First recorded activity by ExcelBanter: Jun 2012 Posts: 3
Formula to return a value in column B conditional upon the value in Column A

Hi!

I'm trying to write a formula in column B to return the value of "NA" if column A say "North America", "EU" if column A says "Europe" and "APAC" if column A says "Asia Pacific".

I'm sure there's something online but I'm not sure how to look for the answer.

#2
June 2nd 12, 03:25 AM posted to microsoft.public.excel.worksheet.functions
 Gord Dibben[_2_] external usenet poster Posts: 572
Formula to return a value in column B conditional upon the value in Column A

There are other methods but here is one.

=IF(A1="","",LOOKUP(A1,{"Asia Pacific","Europe","North
America"},{"APAC","EU","NA"}))

Gord

On Fri, 1 Jun 2012 20:18:13 +0000, JackofAllTrades
> wrote:

>
>Hi!
>
>I'm trying to write a formula in column B to return the value of "NA" if
>column A say "North America", "EU" if column A says "Europe" and "APAC"
>if column A says "Asia Pacific".
>
>I'm sure there's something online but I'm not sure how to look for the
>

#3
June 2nd 12, 08:42 AM
 Spencer101 Senior Member First recorded activity by ExcelBanter: Mar 2012 Posts: 655

Quote:
 Originally Posted by JackofAllTrades Hi! I'm trying to write a formula in column B to return the value of "NA" if column A say "North America", "EU" if column A says "Europe" and "APAC" if column A says "Asia Pacific". I'm sure there's something online but I'm not sure how to look for the answer. Any takers? Thanks in advance!
Two ways of doing this...

1) Nested IF statements

=IF(A1="North America","NA",IF(A1="Europe","EU",IF(A1="Asia Pacific","APAC","")))

2) Lookup table

Put a table somewhere in the workbook that has North America, Europe, South Pacific in one column and NA, EU, APAC in the corresponding cells of the next column. Then use VLOOKUP to return the relevant value.

Hope that helps.

Last edited by Spencer101 : June 2nd 12 at 08:44 AM.
#4
June 3rd 12, 12:05 PM
 JackofAllTrades Junior Member First recorded activity by ExcelBanter: Jun 2012 Posts: 3

Thank you! I'm going to try the nested if statements!

Quote:
 Originally Posted by Spencer101 Two ways of doing this... 1) Nested IF statements =IF(A1="North America","NA",IF(A1="Europe","EU",IF(A1="Asia Pacific","APAC",""))) 2) Lookup table Put a table somewhere in the workbook that has North America, Europe, South Pacific in one column and NA, EU, APAC in the corresponding cells of the next column. Then use VLOOKUP to return the relevant value. Hope that helps.

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post conditional formatting formula to highlight items on column B inrelation to column A hombreazul Excel Discussion (Misc queries) 4 March 4th 12 02:43 PM Field or formula to return last value in a column? Eric Excel Discussion (Misc queries) 7 April 13th 09 04:43 PM Formula to return column letters DoubleZ Excel Discussion (Misc queries) 4 July 22nd 08 09:23 PM Return text in Column A if Column B and Column K match jeannie v Excel Worksheet Functions 4 December 13th 07 07:36 PM column to column conditional formatting won't work, need formula rrupp Excel Worksheet Functions 1 August 23rd 05 10:06 PM

All times are GMT +1. The time now is 03:05 PM.