SOLVED

XLOOKUP

Copper Contributor

Hi there,

I'm new here, but I hope you guys can help me :)

I'm having trouble with an XLOOKUP-formula...


In coulmn A is a list of URL's and in column B is the page name for each URL.


I then use this formula in column C to get the parent of each URL in column A:
=REPLACE([@URL],FIND("@",SUBSTITUTE([@URL],"/","@",LEN([@URL])-LEN(SUBSTITUTE([@URL],"/",""))-1))+1,99,"")


This formula removes the last directory of each URL in column A.
Wonderfull!!!


And here is my issue...
I now want the page name for each of theese parrent URL's in column D...


I'm trying to achieve this with XLOOKUP, but it doesn't seem to work.

My approach is the following:

=XLOOKUP([@[Parent URL]];[@URL];[@[category_name]])

...but all I get is #I/T

Can anyone help me?

 

Thanks in advance

12 Replies

@MIGR_CarlRas Difficult to diagnose based on text alone. First of all, I believe the XLOOPUP formula makes no sense. You are looking up the Parent URL on the current row, in a single cell range, being the URL in the current row and returning the Name in the row. You would want to lookup a single value in an entire table column and return something from another table column if a match is found.

 

Obviously, no match is (or will ever be) found as the Parent URL is never equal the the URL. Hence the #I/T error (#N/A in English) as a result. 

 

Better to upload an example of your file and indicate the end result you want to achieve.

@Riny_van_Eekelen Thank you for your fast reply!

 

I've made a sample sheet to try and clarify my inqury:
MIGR_sample_sheet.xlsx

 

Hope you can help me :)

@MIGR_CarlRas Okay, I see the file but what should be shown in column D? 

@Riny_van_Eekelen In column D I would like the category_name for the parent URL's

@MIGR_CarlRas

So, you wan the part that you chopped off from the URL, but without the last slash? If so, try this:

=SUBSTITUTE(RIGHT([@URL],LEN([@URL])-LEN([@[Parent_URL]])),"/","")

 I believe I entered in into your online file.

@Riny_van_Eekelen 

That is one way of doing it, but unfortunately this isn't sufficient in my case...

Basicly, what I want is a formula that looks in col. C to find a match in col. A and then returns the value of col. B (if there's a match to be found)

Does that make sense?

@MIGR_CarlRas Not really. Sorry. What you have in column C will always be part of what's in column A on the same row and possibly on other rows as well.

 

In your sample file the text string "https://www.example.com/" from column C will be found on every row in column A. Which value from column B would you want to return in column D then?

 

 

@Riny_van_Eekelen 

 

I need [category_name] for the parent URL's based on the static data in col. A and B.

If a cell value in col. D matches a cell value in col. A, I want the value of col. B.

So, for instance in my sample sheet C5 = A2. I then want the value of B2 in cell D5

best response confirmed by MIGR_CarlRas (Copper Contributor)
Solution

@MIGR_CarlRas One last try. See attached file as I don't like to work in the on-line version.

@Riny_van_Eekelen 

EXACTLY! :D
But how does your formula differ from mine?
(See my first post)

Ahh... I see it now!
Yours:
=XOPSLAG([@[Parent_URL]];[URL];[category_name])
vs.
Mine:
=XLOOKUP([@[Parent URL]];[@URL];[@[category_name]])

But I'm not sure why mine doesn't work?

@MIGR_CarlRas 

Mine:

=XLOOKUP([@[Parent_URL]];[URL];[category_name])

Yours:

 

=XLOOKUP([@[Parent URL]];[@URL];[@[category_name]])

Your formula has a few ampersands too many.  Should have made that clear in my first response. Simply got confused when I saw the file and lost track of your intentions. Sorry about that. But, glad that it is solved now!

1 best response

Accepted Solutions
best response confirmed by MIGR_CarlRas (Copper Contributor)
Solution

@MIGR_CarlRas One last try. See attached file as I don't like to work in the on-line version.

View solution in original post