Multiple arrays in INDEX/MATCH function

theishv

New Member
Joined
Jan 18, 2018
Messages
12
Hi guys

The problem which i am facing with the INDEX/MATCH function is that i want to check several columns at once for a certain value.
9


33w5b4l.jpg


The function in the example checks the H column for EMP_ID values.

The column containing EMP_ID values however changes between G and H for different data sets.

Is it possible to create a function which checks both columns?

Theishv
 
Last edited:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Yes.
If you specify under what conditions the column changes which you haven't yet done.
 
Upvote 0
Yes.
If you specify under what conditions the column changes which you haven't yet done.

There is no system to when EMP_ID values appears in column G and when it appears in Column H. An extra column A just appears in some of the data.

Is the solution you propose still valid, and if so can you give me a hint on how to implement it? :)

Theis

 
Upvote 0
That doesnt make sense.
There must be a rule to indicate which column contains the data column G, column H or both.
If the data on a row exists in G and doesnt exist in column H then thats the rule. That if data exists in colunmn G then use that otherwise use column H.

If you cannot see a rule yourself you should supply data icovering reuirements in both columns G and H and specify clearly what column to use.
Perhaps someone else can see a pattern.

At the moment your description of the problem summarises to:

here's some data
sometimes I want column G
sometimes I want column H
But Im not telling you whether I want column G or H in that data

How do you expect us to work out what column should be extracted when you cant explain it yourself?
 
Last edited:
Upvote 0
Consider:

ABCDEFGHIJ
1StatusCompliance_FlagEmp_IDEMP_NAMEDEPT_CODE
2TAKENYATLAnders6234
3TAKENNUBEUlla5132
4MISSINGyAGEAnne5123
5TAKENnIMLIkea5124
6TAKENyGTAGunner6613
7MISSINGyCamilla6613
8
9
10EMP_IDStatus
11IMLTAKEN
12TAKEN
13TAKEN

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet6

Worksheet Formulas
CellFormula
C11=INDEX($A:$AG,MATCH(A11,$H:$H,0),MATCH("STATUS",$1:$1,0))
C12=INDEX($A:$AG,MATCH(A11,INDEX($A:$AG,0,MATCH(A10,$1:$1,0)),0),MATCH("STATUS",$1:$1,0))
C13=INDEX($A:$AG,IFERROR(MATCH(A11,$H:$H,0),MATCH(A11,$I:$I,0)),MATCH("STATUS",$1:$1,0))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



Your formula is in C11. If the header of the column is always in the right place, you can search for the header to know which column to search. That formula is C12. If you can't even count on the header, you can search column H for the ID, and if it's not found, search column I. That formula is C13.

You have different regional settings, so change all the commas to semicolons in these formulas. Hope this helps.
 
Upvote 0
Consider:

ABCDEFGHIJ
1StatusCompliance_FlagEmp_IDEMP_NAMEDEPT_CODE
2TAKENYATLAnders6234
3TAKENNUBEUlla5132
4MISSINGyAGEAnne5123
5TAKENnIMLIkea5124
6TAKENyGTAGunner6613
7MISSINGyCamilla6613
8
9
10EMP_IDStatus
11IMLTAKEN
12TAKEN
13TAKEN

<tbody>
</tbody>
Sheet6

Worksheet Formulas
CellFormula
C11=INDEX($A:$AG,MATCH(A11,$H:$H,0),MATCH("STATUS",$1:$1,0))
C12=INDEX($A:$AG,MATCH(A11,INDEX($A:$AG,0,MATCH(A10,$1:$1,0)),0),MATCH("STATUS",$1:$1,0))
C13=INDEX($A:$AG,IFERROR(MATCH(A11,$H:$H,0),MATCH(A11,$I:$I,0)),MATCH("STATUS",$1:$1,0))

<tbody>
</tbody>

<tbody>
</tbody>



Your formula is in C11. If the header of the column is always in the right place, you can search for the header to know which column to search. That formula is C12. If you can't even count on the header, you can search column H for the ID, and if it's not found, search column I. That formula is C13.

You have different regional settings, so change all the commas to semicolons in these formulas. Hope this helps.

Thank you, works like a charm! :)
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top