1

I have a workbook with two sheets. On the "Likes" sheet, I have a list of URLs (and other things that are not relevant here). The second sheet, "Apr-4.13.22", has topic titles and their URLs. I want to pull the topic titles from "Apr-4.13.22" into the "Likes" sheet. The formula I attempted to use was =VLOOKUP(A2,'Apr-4.13.22'!A:D,1,FALSE) but I received the "Value Not Available" error (tooltip: "A value is not available to the formula or function"). I have confirmed that the value (the URL) is present on the "Apr-4.13.22" sheet.

The URLs are in the first column of my "Likes" sheet. The topic titles are in the first column of the "Apr-4.13.22" sheet, and the URLs are in the second column. I need to pull topic names based on the URL because multiple topics have the same name but different URLs. No URLs are repeats.

Things I tried to fix this that didn't work: I've attempted to shorten the URLs in case they are too long. I attempted removing all the slashes in the URLs. I added a column before the topic name column and updated 1 to 2. I changed A:D to A:Z (just in case). I changed the URL for one row to "fubar" on both sheets. Then, I changed FALSE to TRUE and a topic title was returned; it was the wrong one, though. I attempted to use MATCH and INDEX combined instead, but I don't know enough about either to make a functional formula. (My non-functional formula was =INDEX('Apr-4.13.22'A;A,MATCH(A3,B:B,0)) if it makes a difference.)

Not sure if it's relevant, but I formerly had the "Likes" sheet set up to pull the URL from the "Apr-4.13.22" sheet using the topic name, and that pulled the URLs just fine (though there were some that were repeated due to the repeat topic names issue). The formula I used was =VLOOKUP(A2,'Apr-4.13.22'!A:D,2,FALSE) with the first column being the topic titles.

What am I doing wrong, or should I be taking a different approach entirely? Thank you.

AnnoyedEditor
  • 13
  • 4
  • 1
    A picture would really help here. – findwindow Apr 22 '22 at 19:38
  • VLOOKUP searches the 1st column of the specified range. To seach a different column use XLOOKUP if you have it, or INDEX/MATCH – chris neilsen Apr 22 '22 at 20:04
  • Does the url you search contain over 255 characters? Also, you index columns A:D but you only use the first column. You could then use A:A or you should change `1` to `4` to get the results from column D – P.b Apr 22 '22 at 20:56

1 Answers1

0

VLOOKUP won't work due to the layout of your data on the second sheet. Try XLOOKUP instead

=XLOOKUP(A2,'APR-4.13.22'!B:B,'APR-4.13.22'!A:A)

You were also close with the INDEX and MATCH formula. You just forgot to refer to the right sheet in middle of the MATCH

=INDEX('APR-4.13.22'!A:A,MATCH(A2,'APR-4.13.22'!B:B,0))
mugron
  • 86
  • 4