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

L

#### Legacy 327817

##### Guest
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!!

Last edited by a moderator:

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sorry, the table spreadsheet 2 isn't coming across correctly. The words "Amb, Imm, Diab" are in column A and the numbers "99201, 99202, 99203..." are column B

I think you're going to need a helper column (C) on spreadsheet 2, which counts how many characters there are in column B and then do an Index/Match.

Let me see if I can come up with a formula.

Table 2 is not a Table in the Excel definition....
Each Code, ie 99213 & 99212, should be on separate rows.

"Helper column"

In Spreadsheet 2, C1 - enter the value 0 (zero)

In Spreadsheet 2, C2 - enter the formula =LEN(B1)+C1 and copy it down

"Formula"

Last edited:
Sorry, the table spreadsheet 2 isn't coming across correctly. The words "Amb, Imm, Diab" are in column A and the numbers "99201, 99202, 99203..." are column B

Are we talking of two different workbooks? If so, what are the exact names?

Are we talking of two different workbooks? If so, what are the exact names?

Yes, sorry. I am not sure what the correct terms are, but I have one file, two tabs, each representing a different spreadsheet.

Yes, sorry. I am not sure what the correct terms are, but I have one file, two tabs, each representing a different spreadsheet.

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

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

Sorry for the confusion...

The sheet with 99213 is called 'Sheet1' and the value is in cell in BA2.
The sheet that I am trying to pull the value (aka description) is called 'Sheet2'

Sorry for the confusion...

The sheet with 99213 is called 'Sheet1' and the value is in cell in BA2.
The sheet that I am trying to pull the value (aka description) is called 'Sheet2'

In BB2 of Sheet1 enter:

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

Replies
5
Views
869
Replies
3
Views
587
Replies
0
Views
161
L
Replies
2
Views
239
Replies
5
Views
561

1,203,646
Messages
6,056,531
Members
444,872
Latest member
agutt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back