Find, compare, write higher value - multiple columns and contitions

[Sinimus]

Hi guys,
lets say I have four columns, two with different sets of journal ISSN a two with their respective values, e.g.:

ISSN 1 | Points 1 | ISSN 2 | Points 2
0001-1234 | 100 | 0002-5678 | 110
0002-5678 | 100 | 0001-1234 | 110

My goal is to to find and write (in two new columns) ISSN with higer points or -if not matched by ISSN- write "no match" in first of those two new columns.

Any help would be greatly appreciated, thank you!

[Pepe Le Mokko]

Administrative note

Welcome to the forum

missed the yellow banner advising how to get answers faster by posting a sheet ?

Please take a moment to read it and attach a sheet accordingly.

Thanks you for helping us help you

[Beamernsw]

Hi, a sample sheet always makes it easier for us to help you, but since this was such a small sample of data I thought I'd make it for you.

In cell E2:

Please Login or Register  to view this content.

Edit: Sorry Pepe Le Mokko, I forgot to refresh before I posted. I missed your advice for Sinimus.
Edit 2: Just saw your file now Sinimus, checking it out.

[Beamernsw]

OK, I see now. I thought it sounded too easy
I won't have time to sort this until tomorrow, I'm sure someone else will jump in before I get back.

Three questions though...If there is a match with one column but not the other, do you put the largest number for that matching ISSN or do you put "no match" (In other words, does "no match" mean BOTH ISSN's have no match).
e.g. In row 6 00346861 has a match on row 18, yet 00280836 has no match.

And secondly, where there is a match with one in another row, do you want that answer repeated on those lines as well?
e.g. row 18 will also show the same as row 6 (but if the match in row 18 for 00018732 was higher points it would be shown but it is actually less).

And thirdly, if the points for ISSN 1 and ISSN 2 are the same, which column has preference...meaning which one is listed.

Edit: Added example and 1 more question

[AliGW]

F2:

=IFNA(VLOOKUP(B2,$D$2:$D$50,1,0),"No Match")

G2:

=IFNA(INDEX($A$2:$A$50,MATCH(F2,$B$2:$B$50,0)),"")

H2:

=IFNA(LOOKUP(INDEX($C$2:$C$50,MATCH(F2,$D$2:$D$50,0))-G2,{-5000,0,1},{"bigger than B","same","smaller than B"}),"not found")

[Sinimus]

Yes, its not that simple.
First answer - I would put higher points value only when there is match in ISSN columns (both ISSN columns have matched value). And secondly - yes, then we just remove duplicates.

The case study is we got journals from two different databases - for each db we have for every journal some points from internal metrics. What we want to do is to compare those two dbs, find journal which are in both, compare their points and write higher value.
Thank you kindly for your affords!

[AliGW]

Change G2 to this:

=IFNA(MAX(INDEX($A$2:$A$50,MATCH(F2,$B$2:$B$50,0)),INDEX($C$2:$C$50,MATCH(F2,$D$2:$D$50,0))),"")

and H2 to this:

=IFNA(LOOKUP(INDEX($C$2:$C$50,MATCH(F2,$D$2:$D$50,0))-INDEX($A$2:$A$50,MATCH(F2,$B$2:$B$50,0)),{-5000,0,1},{"bigger than B","same","smaller than B"}),"not found")

[Sinimus]

Wow AliGW, that looks great! I wasnt even thinking about IFNA function. Im going to put it down to our 50k+ rows sheets.
Thanks a million. And thanks to Beamernsw as well.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.