Hello, does anyone know how to use the LOOKUP formula to read a cell if it
contains something.

For example, I have a list showing cad engineer, then on the next row CAD,
the next row Cad technician and so on, all with different descriptions of CAD
In rowns A1:A100

I want my cell to equal CAD if call A1 contains CAD.

I also want it to read other words, like if it contains cad equal CAD, if it
contains ADMIN equal ADM etc....

Thank you! :)

Fiona,


There are several ways but if you have a lot of values to look up then
perhaps a table something like:-

Col b Col C
Cad Ca
Car Cr
Admin Adm
This Tat

Then a formula

=VLOOKUP(A1,B$1:C$4,2,FALSE)


The formula looks in A1 compares it to the left column of B1_C4 and returns
the value iin the right column.

Mike

"Fiona" wrote:

Hello, does anyone know how to use the LOOKUP formula to read a cell if it
contains something.

For example, I have a list showing cad engineer, then on the next row CAD,
the next row Cad technician and so on, all with different descriptions of CAD
In rowns A1:A100

I want my cell to equal CAD if call A1 contains CAD.

I also want it to read other words, like if it contains cad equal CAD, if it
contains ADMIN equal ADM etc....

Thank you! :)

Thanks Mike, that's nearly it, but it becomes unstuck when I have, for
example CAD Engineer in one cell which will be read as CAD, but I also want a
seperate cell for Engineer, but this will also be read as CAD, which would be
incorrect.

I did think I could have something like =IF(C3 "*"&CAD&"*",CAD),
or =IF(C3"*"&HIGHWAY&"*",HIGH)
Am I close?
Thank you for your help.

"Mike H" wrote:

Fiona,


There are several ways but if you have a lot of values to look up then
perhaps a table something like:-

Col b Col C
Cad Ca
Car Cr
Admin Adm
This Tat

Then a formula

=VLOOKUP(A1,B$1:C$4,2,FALSE)


The formula looks in A1 compares it to the left column of B1_C4 and returns
the value iin the right column.

Mike

"Fiona" wrote:

Hello, does anyone know how to use the LOOKUP formula to read a cell if it
contains something.

For example, I have a list showing cad engineer, then on the next row CAD,
the next row Cad technician and so on, all with different descriptions of CAD
In rowns A1:A100

I want my cell to equal CAD if call A1 contains CAD.

I also want it to read other words, like if it contains cad equal CAD, if it
contains ADMIN equal ADM etc....

Thank you! :)

Fiona,

It may be that I'm not understanding correctly but what you describe is no
problem to the original idea I suggested, just extenf the table thus:-

Cad Ca
Car Cr
Admin Adm
This Tat
Cad Engineer Cad
Engineer Cad


An then alte the range in the formula for the array

Mike

"Fiona" wrote:

Thanks Mike, that's nearly it, but it becomes unstuck when I have, for
example CAD Engineer in one cell which will be read as CAD, but I also want a
seperate cell for Engineer, but this will also be read as CAD, which would be
incorrect.

I did think I could have something like =IF(C3 "*"&CAD&"*",CAD),
or =IF(C3"*"&HIGHWAY&"*",HIGH)
Am I close?
Thank you for your help.

"Mike H" wrote:

Fiona,


There are several ways but if you have a lot of values to look up then
perhaps a table something like:-

Col b Col C
Cad Ca
Car Cr
Admin Adm
This Tat

Then a formula

=VLOOKUP(A1,B$1:C$4,2,FALSE)


The formula looks in A1 compares it to the left column of B1_C4 and returns
the value iin the right column.

Mike

"Fiona" wrote:

Hello, does anyone know how to use the LOOKUP formula to read a cell if it
contains something.

For example, I have a list showing cad engineer, then on the next row CAD,
the next row Cad technician and so on, all with different descriptions of CAD
In rowns A1:A100

I want my cell to equal CAD if call A1 contains CAD.

I also want it to read other words, like if it contains cad equal CAD, if it
contains ADMIN equal ADM etc....

Thank you! :)