LOOKUP FUNCTION

[pico]

Iam trying to locate a certain string in a colum and output the partnumber beside the string. For example. I have a table of 2 columns.

*1 1243
1 2345
*2 3456
2 4567
*3 3456
P 2355
S 23556
SS 57744


Iam currently using lookup to look for a particular string and output the data beside it (in the adjacent column).
Example: =lookup(A2,$B$2:$B$9,$C$2:$C$9) where A2 is the string iam looking for in the table and B2:B9 is the first column and C2:C9 is my second. The problem right now is it does not detect the *1 and *2...is there another function i could use to do this?

[OBP]

You can use VBA code to do what you want, there are quite a few examples on this forum that do that type of operation. Using string matching or the Excel "Search" function.

[mdmackillop]

Try

=lookup(A2,$B$2:$C$9)

[pico]

I get a #Value! error when i try to use the lookup function. The lookup function has problems decting the string *1,*2 and *3, but it detects the numbers 1, 2 and 3. I do not want to use VBA for this. I'd like a excel worksheet function.

[Bob Phillips]

Your formula works for me.

[mdmackillop]

I don't know if it's suitable, but try changing the cell format to text. Does that make a difference?

[pico]

no difference when changed to text format

[Bob Phillips]

Post your workbook.

[pico]

I have attached the file and highlighted the information iam looking at in the harness sheet.

[pico]

Corrected formula. There was a error in the previous file

[mdmackillop]

Your formula is

=IF(Sheet4!A1,LOOKUP(Sheet4!A1,HARNESS!$C$144:$D$184),"-")
The Lookup is working, but your formula is defective. Change the first Sheet4!A1 to TRUE or FALSE and you'll get a result. I'm not clear what you are intending by your formula. Can you explain?

[pico]

If cell A1 equals any of the values in the first column of that table then output the number adjacent to the first column. It works for the integer values, but whenever it tries to search for *1, or *2 values it does not recognize it.

[mdmackillop]

I believe that any positive integer is representing TRUE, so you get your return. *2 etc. is meaningless in this context so the formula fails. Enter 0 in A1 and you'll get "-"

so try
=IF(ISNA(LOOKUP(Sheet4!A1,HARNESS!$C$144:$D$184)),"-",LOOKUP(Sheet4!A1,HARNESS!$C$144:$D$184))

BTW, including the If function at the start would have helped here.

[asingh]

Hi,

This seems to work....go to your lookup array, and do a "TEXT-TO-COLUMN".....on the Description Column. Then I used the simple formula:


=VLOOKUP(A1,HARNESS!$C$143:$D$189,2,0)

And it is recognizing cells values padded with a "*".

regards,
asingh

[pico]

What's a text to column?...BTW the vlookup function works .

[pico]

The vlookup function works. But like Md mentioned i'd like a if statement to check if the field is empty or not. If its empty the output shall be error if not do the vlookup. I tried using
=If(JOB_SPEC_FORM!D97,VLOOKUP(JOB_SPEC_FORM!D97,HARNESS!$C$144:$D$189,2,0),"Error")

But then it does not recognize the *2 string if i have the if statement in there.

[mdmackillop]

Check my Post #13

[pico]

Hmnn...I tried the formula again and it works. Md i was talking about your post in my previous comment. I tried it again and it works . Thanks for reminding:beerchug:

[mdmackillop]

Md i was talking about your post
Hi Pico,
No problem. Best though, to use quotes to clarify to whom you are responding. It's not always clear to the respondents.
Regards
MD