Find value in a table on different workbook and return column value

[Legacy 327817]

This one is a little tricky for me, I've tried a lot of formulas but none of them have returned the value I am looking for.

I have a value in spreadsheet 1 called "99213"

I have a table in spreadsheet 2:

A B

1 Ambulatory 99201, 99202, 99203, 99204, 99205, 99211, 99212, 99213
2 Immunization 90698, 90700
3 Diabetes 83036, 83037, 3044F, 3045F, 3046F

In column B of Spreadsheet 2, there are multiple values separated by a comma. The value I want to return for this example is A1: Ambulatory. Is there a simple way to do this? A vlookup isn't working for me. Any help would be greatly appreciated!!

 

[Legacy 327817]

How are these tabs named? And which cell do you have 99213, the value to look for?

Here is my question, now that I have more of an understanding of what I am asking.

I have one excel workbook with two tabs.

Sheet 1 has a value in cell BA2. The value is "99213"

Sheet 2 has a table:

Column A (of sheet 2) is descriptions
1 Ambulatory
2 Immunization
3 Diabetes

(60 rows total of descriptions)

Column B (of sheet 2) has values separated by commas
1 99201, 99202, 99203, 99204, 99205, 99211, 99212, 99213
2 90698, 90700
3 83036, 83037, 3044F, 3045F, 3046F

What I need for excel to do is to look at my value in sheet 1 (99213, cell BA2) and pull the description (column A) in sheet 2 that has the corresponding value. Hope that makes sense!

What also makes this tricky for me is that 99213 may appear in multiple rows of column B (but... I am just trying to get excel to pull a value)

Thank you so much in advance, this would be so helpful is someone knows how to go about this!!

 

[Aladin Akyurek]

See post #10...

 

[shobolos]

The following has been fully tested as working:

Firstly you'll need a "Helper column" -


On Sheet2, in cell C1 - enter the value 0 (zero)


On Sheet2, in cell C2 - enter the formula =LEN(B1)+C1 and copy it down


"Formula"


On Sheet1, in cell BB2 - enter the formula =INDEX(Sheet2!$A$1:$A$3,MATCH(FIND(BA2,Sheet2!$B$1&","&Sheet2!$B$2&","&Sheet2!$B$3),Sheet2!$C$1:$C$3,1))

 

[Aladin Akyurek]

No helper column needed...

See the link: https://dl.dropboxusercontent.com/u/65698317/Mhalle Find value in a table.xlsx

 

[shobolos]

Strange, that didn't work for me, but it clearly does!

 

[Aladin Akyurek]

Strange, that didn't work for me, but it clearly does!

You need to adjust such formulas to your non-American system if you have one, such as period to comma in numeric values.

 

[Legacy 327817]

No helper column needed...

See the link: https://dl.dropboxusercontent.com/u/65698317/Mhalle Find value in a table.xlsx

Thank you for your help, its working for the most part. When I drag the formula down on sheet one, some of them are not picking up the values from sheet two. Sheet two has 60 rows of descriptions and corresponding 5 digit numbers... could that be the reason? Because the formula below has a range of A1:A10, B1:B10?

=LOOKUP(9.99999999999999E+307,FIND(", "&BA2&",",", "&Sheet2!$B$1:$B$10&","),Sheet2!$A$1:$A$10)


Thanks again!

 

[Aladin Akyurek]

Thank you for your help, its working for the most part. When I drag the formula down on sheet one, some of them are not picking up the values from sheet two. Sheet two has 60 rows of descriptions and corresponding 5 digit numbers... could that be the reason? Because the formula below has a range of A1:A10, B1:B10?

=LOOKUP(9.99999999999999E+307,FIND(", "&BA2&",",", "&Sheet2!$B$1:$B$10&","),Sheet2!$A$1:$A$10)


Thanks again!

Just adjust the ranges in the formula.

 

[gmrush]

Just adjust the ranges in the formula.

Aladin:

Could this be adapted to solve the problem I have posted here? I have played around with your formula a bit, but cannot get it to retrieve the data I am seeking.

http://www.mrexcel.com/forum/excel-...n-return-value-another-column-when-found.html

 

[Aladin Akyurek]

Aladin:

Could this be adapted to solve the problem I have posted here? I have played around with your formula a bit, but cannot get it to retrieve the data I am seeking.

http://www.mrexcel.com/forum/excel-...n-return-value-another-column-when-found.html

I think so. Try to provide a small sample (of only what is relevant) along with the expected results. Please do not discuss any formula and more than one problem at a time.