Solved: lookup for part of a cell

[tkaplan]

Good morning
I have 2 spreadsheets - sheet1 and sheet2
sheet 1 has columnA (for easiness sake, lets say rows 1 thru 100) with text values - mostly names of medications. column B has the price

Sheet 2 columnA has names of medications - but does not necessarily include all of the information the sheet1 columnA has.

I need to populate sheet2 columnB by looking to see if the first 10 characters of sheet2 cellA1 appears anywhere in the sheet1 columnA, and then give me the corresponding cost from column B.

I know that if I was looking at the entire cell values of Sheet1ColA I would do a vlookup. I just dont know how to do this looking at anywhere in the cell.

Any help would be greatly appreciated!

[Bob Phillips]

Try this array formula

=INDEX(Sheet1!$B:$B,IF(LEFT($A2,10)=LEFT(Sheet1!$A$2:$A$200,10),ROW(Sheet1! $A$2:$A$200)))

[tkaplan]

Thank you. but wouldnt this search to see if the first 10 chars in col A sheet 2 match the first 10 chars in colA sheet 1? I need it to be the first 10 chars in col A sheet 2 occur anywhere in the cell in Col A sheet 1.....or I may very well be not understanding the formula....

for example
sheet 1
col A col B
soln albuterol 5% 28 mg $4
asthmanex inhaler $10
albuterol tab $6


sheet 2
col A
albuterol

so i want col B on sheet 2 to look at the first 6 chars of col A (in the real spreadsheet the names all have at least 10 so i'd do longer, but for the example above i'll use 6) and find the first instance in sheet 1 col A that it appears - row 1 and return col B ($4). but the "albute" can be anywhere in the cell of sheet 1 col A......



Thanks!

[Bob Phillips]

You mean that a cell in sheet 1 could contain other characters before and/or after the the 10 characters from sheet2?

[tkaplan]

yes. if excel is not the right program for this I can take it into access but i'm not really sure how to do that either...

[BrianMH]

Might be easier to do a macro. Are you just wanting to look up one value or a whole column? If a whole column will there be empty cells?

[tkaplan]

whole column. about 400 values. no empty cells.

[lucas]

post a small example with the results you want on another sheet......

[Bob Phillips]

=INDEX(Sheet1!$B:$B,MIN(IF(ISNUMBER(FIND(LEFT($A2,10),Sheet1!$A$2:$A$200)), ROW(Sheet1!$A$2:$A$200))))

[tkaplan]

Here is a simplified spreadsheet of what I am trying to do.

thanks loads!

[Bob Phillips]

As I said before

=INDEX(Sheet1!$B:$B,MIN(IF(ISNUMBER(FIND(LEFT($A2,10),Sheet1!$A$2:$A$200)), ROW(Sheet1!$A$2:$A$200))))

works perfectly for the matching items, you just need to add a test for mismatches.

[tkaplan]

when i plug the formula in, i get weird numbers......what am i doing wrong? (spreadsheet attached again with formula)

thanks!

[Bob Phillips]

That is because it is an array formula, and you have just Entered it.

Edit the cell, and instead of just hitting Enter, hit Ctl-Shift-Enter. You will see that Excel surrounds the formula in the formula barm with {...} - do not try entering these yourself. If you change the formula, you must again array enter it.

[tkaplan]

Oops. didnt realize it was an array but now it works great! thank you so much!!!