Long time reader, first time poster. I'm at the end of my rope trying to figure out what I'm doing wrong here and could really use some help.
I have two workbooks. One is a large database (WorkBook I/UGLEEORANGE) with ISBNS, titles, corresponding author codes, etc. The second is a list (WorkBook II) of books with ISBNS. I am attempting to return multiple values of matching author codes in WBII. Once the author codes are determined I need to do a lookup into the database with double matching criteria to pull the authors 'share' of the book/ISBN.
To return the multiple results from the lookup value I am using this formula in column B (WBII):
=INDEX([UGLEEORANGE2.xlsx]No_Bookclub_etc!$L$2:$L$14657, SMALL(IF(A2=[UGLEEORANGE2.xlsx]No_Bookclub_etc!$O$2:$O$14657, ROW([UGLEEORANGE2.xlsx]No_Bookclub_etc!$O$2:$O$14657)-MIN(ROW([UGLEEORANGE2.xlsx]No_Bookclub_etc!$O$2:$O$14657))+1, ""), ROW(A1)))
It works for some (maybe 25%) of the spreadsheet, but the rest result in a #NUM error, that I can't figure out.
After I determine the answer for column B. I created a helper column (G in WBII and A in WBI) with the =CONCATENATE formula to serve as my double lookup criteria to find the 'author share' that should return in column M, but even this column will sometimes give me an #NA result even if I know there should be a match when the 'helper' column' is populated. I ate one point entered all of the author codes by hand before I was able to write the INDEX MATCH formula.
I can certainly just continue to search each ISBN and copy and past, but that is very time consuming and I will have to do this a few times a month and would much rather have a formula to save time.
Hopefully I have explained this fully.
I have attached the two workbooks.
Thanks in advance.
*** I had to remove a lot of data and rename the database workbook from UGLEEORANGE2 to uglyorange, so I could upload it onto the site.