IF THEN FUNCTION

  • Thread starter Thread starter Jeanne
  • Start date Start date
J

Jeanne

I need to create a new column and populate it based on the
criteria of two other columns. Example:

Col A
Chicago
Chicago
New York

Col B
NAPS
SPS
NAPS

Criteria
If Col A is Chicago and Col B is NAPS then Col C is 2.0.11
If Col A is Chicago and Col B is SPS then Col C is 2.1.12
If Col A is New York and Col B is NAPS then Col is 2.2.22

The list of criteria is long as is the spreadsheet --
probably 120 combinations of criteria (if A and B = C) and
approximately 3000 records so I need to find the best way
to add the third column. I don't know much about VLookUp
and wondered if that were a solution -- to build a table
of the possibilities first. I anticipate doing this
process of adding a column to a spreadsheet with this
information many times in the future as we migrate test
environments and data.

Thanks!
 
Hi Jeanne,
On Sheet2 make a table like this. The codes are in B1 and C1 (but could go
on and on); the cities are in A2:A4 (but could go on and on)

--------- NAPS SPS
Chicago 2.0.11 2.1.12
Chicago 2.0.99 2.0.99
New York 2.2.22 2.2.99


The 2.2.99 values wee used to fill in where your example had no values.

On Sheet1 I had cites in A and codes in B as in your example. In C1 I have
the formula
=INDEX(Sheet2!$B$2:$C$4,MATCH(A2,Sheet2!$A$2:$A$4,0),MATCH(B2,Sheet2!$B$1:$C
$1,0))
The first MATCH find which row the city is on Sheet2, the second finds what
column the code is in; INDEX then returns the numeric value corresponding to
that pair. Get the absolute references right so you can copy the formula
down the column.

If you name (select cities, use Insert|Name|Define ...) A2:A4 (or A2:A999)
on Sheet2 as Cities, B1:C1 (or B1:X1) as Codes, and B2:C4 (or B2:X999) as
Data, then the formula is somewhat easier to read as:
=INDEX(Data,MATCH(A2,Cities,0),MATCH(B2,Codes,0))

Best wishes
Bernard
www.stfx.ca/people/bliengme'
 
Back
Top