INDEX/MATCH 2D Array.

[Jabberwokki]

Hi There

I have a sheet with columns A through H in use. Row 1 is for headings.

Column A contains uniques I.D's. Columns B through G (Array) contain unique values and some empty cells. Column H uses the TOCOL function to list the values contained in the array vertically removing blanks.
I'd like column I to look up the values in column H and return the unique ID in column A.

I've used INDEX_MATCH and can return the ID from individual columns in the array but not the whole array at the same time.

Happy to upload the sheet for reference.

Any assistance greatly appreciated.

Regards

Dominic

 

[Fluff]

How about

=HSTACK(TOCOL(B2:G22,1),TOCOL(IF(B2:G22<>"",A2:A22,1/0),2))

 

[Jabberwokki]

How about

=HSTACK(TOCOL(B2:G22,1),TOCOL(IF(B2:G22<>"",A2:A22,1/0),2))

Hi Fluff and thank you for your response.

Your formula doesn't seem to omit any blanks or zeros from the array. The formula i'm using for column H is

=FILTER(TOCOL(B2:G21,3,FALSE),(TOCOL(B2:G21,3,FALSE)<>""))

Is there anyway your formula could omit blanks and zeros from the array?

 

[Jabberwokki]

Hi Fluff

After some fiddling about I've managed to sort the ID column 'I' using part of your formula although i'm struggling to wrap my head round how it works! I've retained my:

=FILTER(TOCOL(B2:G21,3,FALSE),(TOCOL(B2:G21,3,FALSE)<>""))

Function to list values in the Array vertically removing blanks and zeros in column 'H' and then used:

TOCOL(IF(B2:G22<>"",A2:A22,1/0),2)

in column 'I' to search the Array and provide the ID....

As i said, not sure how it does it..It's the wrapped IF statement in TOCOL that's confusing me!

Anyways, very much appreciate your help. Thank you.

Regards

Dominic

 

[Fluff]

Your formula doesn't seem to omit any blanks or zeros from the array.

It won't omit zeros, but it will omit blanks as long as the cells are empty, rather than have a formula that returns ""
How about

=HSTACK(TOCOL(IF(B2:G22<>"",B2:G22,1/0),3),TOCOL(IF(B2:G22<>"",A2:A22,1/0),2))

 

[Jabberwokki]

Haha....That works perfectly!

Thank you

 

[Jabberwokki]

Haha....That works perfectly!

Thank you

Can you explain what the else 1/0 does please?
IF statement says 'if array is not blank THEN array ELSE ??'

 

[Fluff]

Can you explain what the else 1/0 does please?

It creates a #DIV/0! error which is the false part of the IF

 

[Jabberwokki]

Which the TOCOL '3' ignores......Sweet.

Thanks again

D

 

[Fluff]

You're welcome & thanks for the feedback.