Mar 18 2022 06:54 AM
Mar 18 2022 06:54 AM
I am in the book business.
I have one spreadsheet that has a list of titles and ISBNs (numbers that identify the book) in one column, and next to it a column that states whether it's hardcover or paperback.
On another spreadsheet there is the list of titles, with two columns for the hardcover and paperbacks. Is there a VLOOKUP or similar function where I can match the ISBN on the first sheet and whether it says hardcover on the column next to it, and direct it to populate the other sheet with said ISBN in the hardcover column? Rinse and repeat for paperback.
Mar 18 2022 07:23 AM
Let's say the first sheet is named Books and looks like this (I know that ISBNs are not single digits, that is just as illustration):
On the other sheet:
The formula in B2 is
Adjust the ranges if your list has more than 300 entries.
This can be filled to the right to C2, then down.
Mar 21 2022 10:32 AM - edited Mar 21 2022 10:37 AM
Your reply has cut down my time on this project considerably: thank you again! I do have to ask though: why would it work on approximately half the titles and not the other half? I've doublechecked to make sure spelling is correct, that formatting is correct (both ISBN columns are formatted as numbers; both title columns are text)...for some of them it simply does not pick up anything and remains blank, even though I know the title exists on the sheet. Not even an error message. This is my code: =IF(VLOOKUP($A2,ISBNList!$A$2:$C$6000,3,FALSE)=B$1,VLOOKUP($A2,ISBNList!$A$2:$C$6000,2,FALSE),"")
I will say this and maybe it'll help: there is not one title on this list that returns both a hardcover and a paperback ISBN, it's either the one or the other, even though both are on the list. Is there something in the code preventing it to pull two values the same title? That just doesn't make sense because I am changing the code to read for the appropriate column header.
Mar 21 2022 12:00 PM
It might help if you made a small sample workbook available by attaching it to a reply or by uploading it to OneDrive. Google Drive or similar and posting a link to it in a reply.
Mar 21 2022 03:12 PM