How to INDEX from an array if a cell matches a different array

**JulianS96** · 03-20-2024, 07:34 AM

Hi forum,

I am scratching my head on how to index from an array if a certain "Name" in a Lookup cell corresponds to a different array (larger than the initial array).

I have attached a mock-up for your convenience with arrows pointing to the correct Matched INDEX for that array.

I have used the FILTER formula to get all the relevant "References" that match the "Name" in the Lookup cell

Thanks in advance,

Julian

EDIT: The cells highlighted in blue are the ones that will have the relevant INDEX formula (if applicable) in.

**windknife** · 03-20-2024, 08:12 AM

Try this in C3 and copy down.

Please Login or Register  to view this content.

**shukla.ankur281190** · 03-20-2024, 08:48 AM

Or Try

Formula:

Please Login or Register  to view this content.

**JulianS96** · 03-20-2024, 09:18 AM

Windknife, thank you for your solution! I've tried to understand how it works by breaking it down and can sort of see what's going on here but will take it regardless!

shukla, That function is amazing, in how it generates the correct pairing for ALL the cells in the column with just one formula entered in the top cell!

The only problem is that the formula does not recognise "807.pdf" and generates a #CALC error.

Thanks again both for your solutions!

**DJunqueira** · 03-20-2024, 09:47 AM

Another spill array formula.

Formula:

Please Login or Register  to view this content.

**JulianS96** · 03-20-2024, 10:12 AM

Djunqueira, that one works perfect, thanks!

**windknife** · 03-20-2024, 10:17 AM

You are welcome.

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

**DJunqueira** · 03-20-2024, 10:27 AM

Tks for the feedback, glad to have helped.

How to INDEX from an array if a cell matches a different array

LinkBack

Thread Tools

Rate This Thread

Display

How to INDEX from an array if a cell matches a different array

Re: How to INDEX from an array if a cell matches a different array

Re: How to INDEX from an array if a cell matches a different array

Re: How to INDEX from an array if a cell matches a different array

Re: How to INDEX from an array if a cell matches a different array

Re: How to INDEX from an array if a cell matches a different array

Re: How to INDEX from an array if a cell matches a different array

Re: How to INDEX from an array if a cell matches a different array

Thread Information

Users Browsing this Thread

Similar Threads

Create an array for matches of a source array to any values in a match array.

Approval Matrix with 4 different lookup criteria. Index matches? Array? Not sure...

Index matched array with duplicated matches, help needed!

lookup an array in a row in a index and return a value if array matches any row in index

[SOLVED] Index Match array formula need to pull names whose adjacent cell matches given criteria

[SOLVED] INDEX MATCH array formula that matches substring n gives multiple matches

if one cell matches an array then output specific cell from array

Bookmarks

Bookmarks

Posting Permissions