I have a column in a large spreadsheet called "warehouse". "Warehouse" (A2
thru A7500) can be one of 25 possible names (MOreturns, MOinv, MOjunk,
TXreturns, TX2returns, TXjunk, AZreturns, AZ2, etc.). I am inserting a column
(B) where I want the warehouse location to populate unless it is a "junk"
warehouse, i.e. if A2=MOreturns, B2=MO; if A3=TXreturns, B3=TX; if A4=AZjunk,
B4=blank...
I tried using INDEX to match a name list but since it's not a 1:1
relationship, I couldn't get it to work and for some reason my nested IF
function came back saying one of my values was the wrong data type (I
enclosed the text in " " so I'm not sure what the "wrong type" was).
Would a VLOOKUP function work best or a nested IF function? Please give an
example of the formula. Please be specific. Thanks!

Susan --

If it were me, I'd use VLOOKUP. Set up a little table with the warehouse
names in the left column, then the location in the next column. Then use the
VLOOKUP function to get it working in one cell, and then double-click on the
little square dot in the lower right corner of that dot to copy the formula
all the down to row 7500. Remember to make the reference cells in the
vlookup table absolute (with '$') so that all the rows look to the same small
range.

HTH

"Susan" wrote:

I have a column in a large spreadsheet called "warehouse". "Warehouse" (A2
thru A7500) can be one of 25 possible names (MOreturns, MOinv, MOjunk,
TXreturns, TX2returns, TXjunk, AZreturns, AZ2, etc.). I am inserting a column
(B) where I want the warehouse location to populate unless it is a "junk"
warehouse, i.e. if A2=MOreturns, B2=MO; if A3=TXreturns, B3=TX; if A4=AZjunk,
B4=blank...
I tried using INDEX to match a name list but since it's not a 1:1
relationship, I couldn't get it to work and for some reason my nested IF
function came back saying one of my values was the wrong data type (I
enclosed the text in " " so I'm not sure what the "wrong type" was).
Would a VLOOKUP function work best or a nested IF function? Please give an
example of the formula. Please be specific. Thanks!

*Maybe* this entered in column B and copied down:

=IF(COUNTIF(A1,"*junk*"),"",LEFT(A1,2))


--
Biff
Microsoft Excel MVP


"Susan" wrote in message
...
I have a column in a large spreadsheet called "warehouse". "Warehouse" (A2
thru A7500) can be one of 25 possible names (MOreturns, MOinv, MOjunk,
TXreturns, TX2returns, TXjunk, AZreturns, AZ2, etc.). I am inserting a
column
(B) where I want the warehouse location to populate unless it is a "junk"
warehouse, i.e. if A2=MOreturns, B2=MO; if A3=TXreturns, B3=TX; if
A4=AZjunk,
B4=blank...
I tried using INDEX to match a name list but since it's not a 1:1
relationship, I couldn't get it to work and for some reason my nested IF
function came back saying one of my values was the wrong data type (I
enclosed the text in " " so I'm not sure what the "wrong type" was).
Would a VLOOKUP function work best or a nested IF function? Please give an
example of the formula. Please be specific. Thanks!

Thanks for the help - the Lookup Table instructions on the Contextures
website filled in the blanks.
Susan


"pdberger" wrote:

Susan --

If it were me, I'd use VLOOKUP. Set up a little table with the warehouse
names in the left column, then the location in the next column. Then use the
VLOOKUP function to get it working in one cell, and then double-click on the
little square dot in the lower right corner of that dot to copy the formula
all the down to row 7500. Remember to make the reference cells in the
vlookup table absolute (with '$') so that all the rows look to the same small
range.

HTH

"Susan" wrote:

I have a column in a large spreadsheet called "warehouse". "Warehouse" (A2
thru A7500) can be one of 25 possible names (MOreturns, MOinv, MOjunk,
TXreturns, TX2returns, TXjunk, AZreturns, AZ2, etc.). I am inserting a column
(B) where I want the warehouse location to populate unless it is a "junk"
warehouse, i.e. if A2=MOreturns, B2=MO; if A3=TXreturns, B3=TX; if A4=AZjunk,
B4=blank...
I tried using INDEX to match a name list but since it's not a 1:1
relationship, I couldn't get it to work and for some reason my nested IF
function came back saying one of my values was the wrong data type (I
enclosed the text in " " so I'm not sure what the "wrong type" was).
Would a VLOOKUP function work best or a nested IF function? Please give an
example of the formula. Please be specific. Thanks!

Tried this also - that's a nifty trick - I'll have to save it for future use!
Susan


"T. Valko" wrote:

*Maybe* this entered in column B and copied down:

=IF(COUNTIF(A1,"*junk*"),"",LEFT(A1,2))


--
Biff
Microsoft Excel MVP


"Susan" wrote in message
...
I have a column in a large spreadsheet called "warehouse". "Warehouse" (A2
thru A7500) can be one of 25 possible names (MOreturns, MOinv, MOjunk,
TXreturns, TX2returns, TXjunk, AZreturns, AZ2, etc.). I am inserting a
column
(B) where I want the warehouse location to populate unless it is a "junk"
warehouse, i.e. if A2=MOreturns, B2=MO; if A3=TXreturns, B3=TX; if
A4=AZjunk,
B4=blank...
I tried using INDEX to match a name list but since it's not a 1:1
relationship, I couldn't get it to work and for some reason my nested IF
function came back saying one of my values was the wrong data type (I
enclosed the text in " " so I'm not sure what the "wrong type" was).
Would a VLOOKUP function work best or a nested IF function? Please give an
example of the formula. Please be specific. Thanks!