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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
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
 
Upvote 0
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.
 
Upvote 0
Table 2 is not a Table in the Excel definition....
Each Code, ie 99213 & 99212, should be on separate rows.
 
Upvote 0
"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"

In spreadsheet 1, B1 (presuming the value 99213 resides in A1) - enter the formula =INDEX('[Spreadsheet 2.xlsx]Sheet1'!$A$1:$A$3,MATCH(FIND(A1,'[Spreadsheet 2.xlsx]Sheet1'!$B$1&","&'[Spreadsheet 2.xlsx]Sheet1'!$B$2&","&'[Spreadsheet 2.xlsx]Sheet1'!$B$3),'[Spreadsheet 2.xlsx]Sheet1'!$C$1:$C$3,1))
 
Last edited:
Upvote 0
Mhalle said:
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
Click to expand...

Are we talking of two different workbooks? If so, what are the exact names?
 
Upvote 0
Aladin Akyurek said:
How are these tabs named? And which cell do you have 99213, the value to look for?
Click to expand...

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'
 
Upvote 0
Mhalle said:
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'
Click to expand...

In BB2 of Sheet1 enter:

=LOOKUP(9.99999999999999E+307,FIND(", "&BA2&",",", "&Sheet2!$B$1:$B$10&","),Sheet2!$A$1:$A$10)
 
Upvote 0
You must log in or register to reply here.

Similar threads

4
  • Question
Lookup 2nd Occurrence of Value Starting From Bottom Row And Return Value In Different Column
Replies
5
Views
1K
Fluff
Fluff
M
  • Solved
Find maximum values in table and return values from different columns in the same table.
Replies
3
Views
616
Fluff
Fluff
C
  • Question
Finding Table Entry and Returning Values
Replies
0
Views
169
carstvn
C
L
  • Question
Need help reversing table
Replies
2
Views
253
MickG
M
K
  • Question
INDEX MATCH to search two different columns and return value
Replies
5
Views
593
Eric W
Eric W

Forum statistics

Threads
1,214,393
Messages
6,119,261
Members
448,880
Latest member
aveternik

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top