How to lookup for the nth value in a lookup function ?

Question

Level 1

6 points

How to lookup for the nth value in a lookup function ?

Hi there everyone,

I'm a bit stuck, I have to admit...

I'm actually trying to randomize results of a certain column, ONLY if they match a certain criteria in another column, and I still don't get it.

First problem, I have 2 columns. The first contains numbers (some are regularly similar), and the second contains names.

In another table, I have one cell that specify the number I'm searching for. and beside the cell where I'm putting my formula to get the result.

BUT, I want to find a random nth match and not necessarily the first encountered.

I understand how to ask for "first to last" ; "last to first" ; exact match, etc. Even the binary results (which I definitely don't understand... which is actually giving me the second result starting off the first or last... why second ? Why not being able to specify "nth" match ?)...

However,

to make it simple and clear :

I would like a formula that will give me the nth (random) name in the column that matches the specified number in the other cell. "Give me a random name of this column B that actually has the number 9 (for example) in the column A".

Anyone could, at least, explain me how to ask for the... 4th result of the column that matches the criteria, instead of the first one ? It should save me a lot of time and mental health ! :)

Thanks in advance for your tips !

MacBook Air 13″, macOS 14.2

Posted on Mar 17, 2024 7:11 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

30,708 points

Mar 17, 2024 8:21 PM in response to NelCaffrey

It would be nice if there was a lookup function that would provide all results and for which INDEX could be used to get them. But there is not such function. It can be done with a helper column, though.

Formula in Table 1::C2

=$A2&"-"&COUNTIF(A$2:A2,A2)

or

=$A2&"-"&COUNTIF(OFFSET(A2,2−ROW(),0,ROW()−1,1),A2)

I am providing the second formula for if you need to be able to sort the table of names. Due to a longstanding bug in Numbers that I hope they deem worthy of fixing one day (and I make sure to mention every time I have to write a stupid formula like this to work around the bug), the COUNTIF function will break in some rows if the table is sorted.

Fill down to the end of the column with whichever formula you use.

Formula in Table 1-1::B4

=XLOOKUP(B2&"-"&B3,Table 1::C,Table 1::B,"Not Found")

You can hide Table 1 column C after it is all set up and working.

Reply

Answer 2

SGIII

Level 8

37,562 points

Mar 18, 2024 9:56 AM in response to NelCaffrey

NelCaffrey wrote:

I would like a formula that will give me the nth (random) name in the column that matches the specified number in the other cell. ... how to ask for the... 4th result of the column that matches the criteria, instead of the first one ?

You can do this with one formula and no helper columns, like this:

Only one formula:

=IFERROR(REGEX.EXTRACT(TEXTJOIN("~",1,Table 1::A:B),"\b"&B1&"~(\w+)",B2,1),"Not found")

Use ; instead of , in the formula if your regions uses , as the decimal separator.

The table at the bottom of the screenshot is not needed. I include it to show the details of how this works.

TEXTJOIN creates a string in the form shown.

REGEX.EXTRACT grabs the name from the second column corresponding to the nth match, where n is in B2.

The IFERROR handles the case where there is no match.

REGEX.EXTRACT - Apple Support (CA)

SG

Reply

Answer 3

NelCaffrey Author

Level 1

6 points

Mar 18, 2024 9:12 AM in response to Badunit

Hello Badunit, and thank you very much !

Actually I had already done this as a workaround, pretty much like yours. But I hoped there were a simple magic formula like "Do whatever I need and ask no question, Mr Numbers !"... but no. :D

Very useful for this second formula though. Don't know if I'm gonna have to sort it but... actually we never know !

And the more you work on a complex sheet, the more you want to avoid the "****, why did I put this in that row 2 years ago ?!! Nothing works normally..."

Thanks again for the time spent on my issue! I appreciate :)

Reply

Answer 4

Badunit

Level 7

30,708 points

Mar 19, 2024 2:52 AM in response to NelCaffrey

REGEX.EXTRACT with TEXTJOIN is a great one-cell choice. I suggest one change to the formula so that it will return names that have spaces , hyphens, and whatever other non-letter characters might be in a name such as Mary Beth, Mr. & Mrs. Jack Waters, Ashley Smith-Jones, etc.

replace "\b"&B1&"~(\w+)"

with "\b"&B1&"~([^~]+)"

This will return everything in the cell except for a "~"

Reply