LOOKUP instead of IF
 

I am trying to create a formula that searches for text in a column and
returns a matching number based on the text that is entered in that cell.

A portion of the formula that I created is below where my real list is 19
rows long, which is why I can't use the IF formula.

=LOOKUP(D13,{EUE,NUE,STC,LTC},{"0150","0151","0152 ","0153"})

When I enter this formula and check the function, the LOOKUP_Vector reads
"Invalid".

I have used the same formula with success in the same workbook, but the
look-up vector is numbers instead of text.

Any suggestions?

LOOKUP instead of IF
 

put quote marks around the text
=LOOKUP(D13,{"EUE","NUE","STC","LTC"},{"0150","015 1","0152","0153"})
will work

"cstruthers" wrote:

I am trying to create a formula that searches for text in a column and
returns a matching number based on the text that is entered in that cell.

A portion of the formula that I created is below where my real list is 19
rows long, which is why I can't use the IF formula.

=LOOKUP(D13,{EUE,NUE,STC,LTC},{"0150","0151","0152 ","0153"})

When I enter this formula and check the function, the LOOKUP_Vector reads
"Invalid".

I have used the same formula with success in the same workbook, but the
look-up vector is numbers instead of text.

Any suggestions?

LOOKUP instead of IF
 

=LOOKUP(D13,{EUE,NUE,STC,LTC},{"0150","0151","0152 ","0153"})

The lookup_vector must be sorted ascending and you're missing " ":

=LOOKUP(D13,{"EUE","LTC","NUE","STC"},{"0150","015 1","0152","0153"})

If you have 19 pairs of data you'd be better off making a lookup table on
your sheet and referring to that table rather than hardcoding all that into
a formula.

Biff

"cstruthers" wrote in message
...
I am trying to create a formula that searches for text in a column and
returns a matching number based on the text that is entered in that cell.

A portion of the formula that I created is below where my real list is 19
rows long, which is why I can't use the IF formula.

=LOOKUP(D13,{EUE,NUE,STC,LTC},{"0150","0151","0152 ","0153"})

When I enter this formula and check the function, the LOOKUP_Vector reads
"Invalid".

I have used the same formula with success in the same workbook, but the
look-up vector is numbers instead of text.

Any suggestions?

LOOKUP instead of IF
 

Thanks for this. What would the code look like for referring to a table
rather than hardcoding my 19 pieces of data?

"T. Valko" wrote:

=LOOKUP(D13,{EUE,NUE,STC,LTC},{"0150","0151","0152 ","0153"})

The lookup_vector must be sorted ascending and you're missing " ":

=LOOKUP(D13,{"EUE","LTC","NUE","STC"},{"0150","015 1","0152","0153"})

If you have 19 pairs of data you'd be better off making a lookup table on
your sheet and referring to that table rather than hardcoding all that into
a formula.

Biff

"cstruthers" wrote in message
...
I am trying to create a formula that searches for text in a column and
returns a matching number based on the text that is entered in that cell.

A portion of the formula that I created is below where my real list is 19
rows long, which is why I can't use the IF formula.

=LOOKUP(D13,{EUE,NUE,STC,LTC},{"0150","0151","0152 ","0153"})

When I enter this formula and check the function, the LOOKUP_Vector reads
"Invalid".

I have used the same formula with success in the same workbook, but the
look-up vector is numbers instead of text.

Any suggestions?

LOOKUP instead of IF
 

if your text were in C20 through C40
and the associated numbers in D20 through D40
try
=vlookup(D13,C20:D40,2)

"cstruthers" wrote:

Thanks for this. What would the code look like for referring to a table
rather than hardcoding my 19 pieces of data?

"T. Valko" wrote:

=LOOKUP(D13,{EUE,NUE,STC,LTC},{"0150","0151","0152 ","0153"})

The lookup_vector must be sorted ascending and you're missing " ":

=LOOKUP(D13,{"EUE","LTC","NUE","STC"},{"0150","015 1","0152","0153"})

If you have 19 pairs of data you'd be better off making a lookup table on
your sheet and referring to that table rather than hardcoding all that into
a formula.

Biff

"cstruthers" wrote in message
...
I am trying to create a formula that searches for text in a column and
returns a matching number based on the text that is entered in that cell.

A portion of the formula that I created is below where my real list is 19
rows long, which is why I can't use the IF formula.

=LOOKUP(D13,{EUE,NUE,STC,LTC},{"0150","0151","0152 ","0153"})

When I enter this formula and check the function, the LOOKUP_Vector reads
"Invalid".

I have used the same formula with success in the same workbook, but the
look-up vector is numbers instead of text.

Any suggestions?

LOOKUP instead of IF
 

Thanks Sooooo Much! This helped me a ton!


"bj" wrote:

if your text were in C20 through C40
and the associated numbers in D20 through D40
try
=vlookup(D13,C20:D40,2)

"cstruthers" wrote:

Thanks for this. What would the code look like for referring to a table
rather than hardcoding my 19 pieces of data?

"T. Valko" wrote:

=LOOKUP(D13,{EUE,NUE,STC,LTC},{"0150","0151","0152 ","0153"})

The lookup_vector must be sorted ascending and you're missing " ":

=LOOKUP(D13,{"EUE","LTC","NUE","STC"},{"0150","015 1","0152","0153"})

If you have 19 pairs of data you'd be better off making a lookup table on
your sheet and referring to that table rather than hardcoding all that into
a formula.

Biff

"cstruthers" wrote in message
...
I am trying to create a formula that searches for text in a column and
returns a matching number based on the text that is entered in that cell.

A portion of the formula that I created is below where my real list is 19
rows long, which is why I can't use the IF formula.

=LOOKUP(D13,{EUE,NUE,STC,LTC},{"0150","0151","0152 ","0153"})

When I enter this formula and check the function, the LOOKUP_Vector reads
"Invalid".

I have used the same formula with success in the same workbook, but the
look-up vector is numbers instead of text.

Any suggestions?