Looking for alternative to array function for looking up 1 criteria and listing all result

[kthom]

Hi everyone,

I am trying to find a way that I can look up a single criteria in one column and return a corresponding row value for all occurrences.

I need to perform this operation over 20 times, so as you can imagine using an array function glues up the system very quickly.

Another way to describe this could be to say if I was using VLOOKUP() with the capability to return multiple values in their own cells-without using a method that drains so much memory.

I envision a function that offsets the search range when a look up is successful, but this is where my ideas run out. Perhaps there is a simple way to do this using VBA?

I would really appreciate any assistance or inspiration.

Thank you all!!

[Glenn Kennedy]

Hi Here's a non-array INDEX-MATCH (which I first saw nflsales use, but has maybe been around for much longer). Try this out. Y

[TMS]

@Glenn: that is very clever. +1 to nflsales

[Glenn Kennedy]

@Glenn: that is very clever. +1 to nflsales

Indeed. I (think/hope) I added Reputation when I saw that one...

[kthom]

What an absolute gem! This should work very nicely for what I have in mind. Thank you so much for the link!

[Glenn Kennedy]

Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.