Is it possible to match two values on a spreadsheet to a value on another?

New Contributor

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.

 

TIA!

4 Replies

@newbookswing 

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):

S1220.png

On the other sheet:

S1221.png

The formula in B2 is

=IF(VLOOKUP($A2,Books!$A$2:$C$300,3,FALSE)=B$1,VLOOKUP($A2,Books!$A$2:$C$300,2,FALSE),"")

Adjust the ranges if your list has more than 300 entries.

This can be filled to the right to C2, then down.

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.

@newbookswing 

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.

Hi hi, I'm going to message you privately with it because it's related to my work.