Index match reading across multiple columns - is it possible?

[mightybracket123]

Hi all,

I'm a bit stuck trying to figure out how to set up an index match. I feel like this is a really simple thing that I'm trying to do but I'm not sure why it doesn't work and whilst I've tried googling the answer, I'm not really sure what to be searching for.

In the attached example, this represents how I have a large data set laid out.

Every item has a specific data value which sits in one of a number of columns - but I won't know which column the data value sits in.

I'm trying to do an index match to find the item in column A within column G and then bring back whatever associated value sits in columns H-K....but it always returns #Ref

I know that usually for an index match you'd have the data just sitting in one column but I have a large data set set up like this and I can't figure out how to make this work.... if it's at all possible?

Would anyone be able to help point me in the right direction to work around this?


Thanks

[Richard Buttrey]

Hi

Are the numbers in A3:A12 always the same numbers and in the same relative position as in G3:G12.

i.e. does say A5 always equal G5

[mightybracket123]

Hi Richard,

thanks for looking into this and for your reply.

Sadly not. Where I've put my "data", this is just to represent how my data is laid out as it sits in a different tab.

My list of items in column A will be in a different order to how they appear in column G unfortunately.

[Glenn Kennedy]

With an array formula:

=IFERROR(INDIRECT(TEXT(SMALL(IF(($H$3:$K$12<>""),ROW($H$3:$K$12)*100+COLUMN(H3:K12),10^10),ROWS(C$3:C3)),"R0C00"),FALSE),"")

In your sample, the results read left to right. So does this.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[mightybracket123]

Hi Glenn,

Thanks for taking the time to look into this !

Could I ask what the formula is doing in these parts:

ROW($H$3:$K$12)*100 - why is this *100?

+COLUMN(H3:K12),10^10) - why is the 10^10

"R0C00" - what does this mean?


Sorry for the questions, I'm just not sure how I would be able to replicate the formula if I needed to change any of these parts and I don't understand what they mean / what they are doing.

Thanks again

[Glenn Kennedy]

The whole point of the formula is to turn all the non-blank values into a number which can be interpreted in RC (row column) notation. It adds the row number of the non-blank cells (multiplied by a smallish number, 100) to the column number. So something in A4 would calculate as 100x1 + 4 = 104. Blank values are given the value 10^10 (10, with 10 zeros after it... a massive number).

The formula then returns the values in ascending RC order. By multiplying the row number by 100 and not multiplying the column number, the array is returned row by row, as opposed to column by column. If you want it to work on MUCH larger arrays, let me know.

But for smallish arrays, just adjust the ranges of all parameters to cover the range of the values.



You're welcome.

---

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 all members who helped you reach a solution.

[Richard Buttrey]

Hi Richard,

thanks for looking into this and for your reply.

Sadly not. Where I've put my "data", this is just to represent how my data is laid out as it sits in a different tab.

My list of items in column A will be in a different order to how they appear in column G unfortunately.

That's why I asked since it would have allowed a simpler result.
Here's one way

Please Login or Register  to view this content.

[BMV]

@Glenn Kennedy . I'm not sure indirect is good for "In the attached example, this represents how I have a large data set laid out"
I preferred

Please Login or Register  to view this content.

@mightybracket123. Is there blank row in your real data? Is there row with more one value in your real data?

@Richard Buttrey Why volitile?'

Please Login or Register  to view this content.

[Glenn Kennedy]

@BMV. Possibly so!! We'll see. Here is another volatile version, but this time, non-array.

=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW($H$3:$K$12)*100+COLUMN($H$3:$K$12))/($H$3:$K$12<>0),ROWS(I$30:I30)),"R0C00"),FALSE),"")

I am still looking at your formula. There are some features in it that I am not familiar with and cannot quite understand. Have some rep!!

[Glenn Kennedy]

OK, BMV...

IFERROR(INDEX($1:$1048576,SMALL(IF(($H$3:$K$12<>""),ROW($H$3:$K$12)),ROWS(C$3:C3)),MOD(SMALL(IF(($H$3:$K$12<>""),ROW($H$3:$K$12)+COLUMN($H$3:$K$12)%),ROWS(C$3:C3)),1)/1%+0.1),"")

1. What is the function of the 0.1? It does not result in the production of an integer for the column reference for INDEX. Indeed, it doesn't seem to need one (something I never realised!!)

2. I have never seen the construction using the % in blue before. Interesting!!

3. Based on your formula... how about a non-array version?

Please Login or Register  to view this content.

[BMV]

@Glenn
1. +0.1 ( sorry I have replaced separator ; to , but forgot about , to . ) . The result of divide can be less the we hope. 2<>1.999999999999. the shortest way to get 2 - add 0.1.Index will get integer value.
2. % - equivalent to /100 ? /1% = /0,01 = *100
3. Yes, aggregate is useful.

[Richard Buttrey]

@Richard Buttrey Why volitile?'

Please Login or Register  to view this content.

Why do you query INDEX being volatile? AFAIAA it hasn't been volatile since Excel 97

[BMV]

Why do you query INDEX being volatile? AFAIAA it hasn't been volatile since Excel 97

Not main. Yours =INDEX($G$3:$K$12,MATCH($A3,$G$3:$G$12,FALSE),MATCH("Y",OFFSET($G$2:$K$2,C3,0)))

[mightybracket123]

Hi all,

Firstly thank you all so much for taking the time to look into this, I didn't expect there to be so many replies when I just logged on this morning !

@ Glen thank you for your explanations to my previous questions and for your further input on this!
@ BMV there are the odd few blank rows but only one value per row. Thanks for taking the time to look into this.
@ Richard, thank you as well for looking into this and your help with this.


I'm not really sure what a lot of these formulas are doing but I will have a play around with them to see if I can get them working in my "live" data set.

Thanks again,