Index/Match cross search and extract

hi guys i need a little help, ill try and explain my situation as best i can

sheet 2
column C is list of chemicals (to give context)
column A is a list of the publication numbers they apear in
column B is GOING to be the list of publication titles they are in

sheet 3
column A is a complete list of every publication number in our library
column B is the corresponding publication title

what i would like to do is extract the title from sheet 3 and input it into column B sheet 2 corresponding to whatever pub number is listed in column A of sheet 2

the code i have so far is in column b sheet 2 "=INDEX(Sheet3!B:B,MATCH(A2,Sheet3!A:A,0))"

now the problem i have is this works perfectly when only one pub is listed in a cell in column A sheet 2 but when there is more than one pub listed (ie "pub number" next line in cell "another pub number") it gives a N/A result.

is there anyway to call up multiple pub titles from the multiple pub numbers within one cell

cheers for all your help

Hi,

so you may have in one cell several publication numbers, like

A1 = "ABC123, ABC345"

The easiest fix for that would be to split this record into two rows

A1 = "ABC123"
A2 = "ABC345"

and then list each publication in its own row. Excel's not particularly fond of several values in one cell, and especially when using lookup functionality, it can be a real pain.

However, it might be doable, if there were only ever a maximum number of publication numbers in one cell, say no more than three ...

Can you upload a small data sample that illustrates how the multi pub number thing looks?
You can upload a file by clicking "Go Advanced" below and then the paper clip icon.

cheers

Now that was fun

In the attached file I've drawn up a solution that works this way:

You will need ten helper columns to the right of the table in Sheet1. It does not matter how far right they are. I suggest that you insert as many columns as you need to separate the formulas from the original data table, then copy and paste the formulas into the respective columns in your original sheet.

how it works
If there is only one reference number in column A, the normal INDEX/MATCH will be performed. I did not change your formula, just nested it inside the IF()

If a char(10) is found in column A, my formulas in columns J ff. kick in. I have provided for up to ten pub numbers separated by alt-enter or char(10). Any other delimiter will not work.

In the ten columns J to S you will either see #Value! or a publication title.
If there are two pub numbers, you will see the first and the last column populated
If there are three pub numbers, you will see the first, second and last column populated

Column B uses a simple concatenation with char(10) to put them all back together into one cell.

I had to separate out the individual pub numbers. For your benefit, you can study these formulae in column V to AE. When you're done, you can delete these 10 columns

First column separates out the first pub number with a LEFT() function. No big deal.
Then it gets tricky. A combination of FIND() and SUBSTITUTE() is nested in a MID() statement to single out where the next char(10) is, how long the string to yet the next char(10) is and returns the result.
For the last column I use a combination of RIGHT(), SEARCH() and SUBSTITUTE() to separate the bit that comes after the last occurrence of char(10)

If you look at columns J to S, you will see that I took the formulae from V to AE respectively and inserted them into your INDEX/MATCH, where MATCH had the lookup reference A3.

Looks awful, but works.

Theoretically, the formulae in J to S could be concatedated directly in B, but this would be a bit unwieldy and we might get close to the cell character limit.

Once you position the columns nicely out of sight, you can also hide them. Shouldn't make a difference.

one important thing When you copy the formulae from this workbook to your original workbook, make sure to follow this procedure:

- Insert as many columns before J to move the columns so far right that they will be out of the way in your original worksheet, say AA
- Then, select cell AA2 and hit F2 to go to formula editing mode. Copy the formula from the formula editor
- next go to your original workbook, select AA2, hit F2 and paste.

If you just copy and paste cell AA2, you may run the risk that you will end up with a reference to my workbook's Sheet3 instead of your original workbook's sheet 3, and, believe me, you don't want that!

Repeat the same F2 copy and F2 paste procedure for all other 9 columns. Then simply copy the cells down the length of your table.

Pheww!

worksheet attached. Let me know how you get on.

cheers

HOLY KLEENEX BATMAN

THANKYOU SO MUCH

thats an amazing bit of code, WAY out of my league, thankyou for the time and effort you put into it.

ive yet to put it into my full copy but i did some tests on a mid size data set and it worked really well

again thankyou so much

(note to mods i will make this solved when i have tried it on full size spreadsheet)