Multiple arrays in INDEX/MATCH function

T

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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
Yes.
If you specify under what conditions the column changes which you haven't yet done.
 
Upvote 0
Special-K99 said:
Yes.
If you specify under what conditions the column changes which you haven't yet done.
Click to expand...

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
StatusCompliance_FlagEmp_IDEMP_NAMEDEPT_CODE
TAKENYATLAnders
TAKENNUBEUlla
MISSINGyAGEAnne
TAKENnIMLIkea
TAKENyGTAGunner
MISSINGyCamilla
EMP_IDStatus
IMLTAKEN
TAKEN
TAKEN

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]6234[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5132[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5123[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5124[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]6613[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]6613[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C11[/TH]
[TD="align: left"]=INDEX($A:$AG,MATCH(A11,$H:$H,0),MATCH("STATUS",$1:$1,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C12[/TH]
[TD="align: left"]=INDEX($A:$AG,MATCH(A11,INDEX($A:$AG,0,MATCH(A10,$1:$1,0)),0),MATCH("STATUS",$1:$1,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C13[/TH]
[TD="align: left"]=INDEX($A:$AG,IFERROR(MATCH(A11,$H:$H,0),MATCH(A11,$I:$I,0)),MATCH("STATUS",$1:$1,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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
Eric W said:
Consider:

ABCDEFGHIJ
StatusCompliance_FlagEmp_IDEMP_NAMEDEPT_CODE
TAKENYATLAnders
TAKENNUBEUlla
MISSINGyAGEAnne
TAKENnIMLIkea
TAKENyGTAGunner
MISSINGyCamilla
EMP_IDStatus
IMLTAKEN
TAKEN
TAKEN

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]6234[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5132[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5123[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5124[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]6613[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]6613[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C11[/TH]
[TD="align: left"]=INDEX($A:$AG,MATCH(A11,$H:$H,0),MATCH("STATUS",$1:$1,0))[/TD]
[/TR]
[TR]
[TH]C12[/TH]
[TD="align: left"]=INDEX($A:$AG,MATCH(A11,INDEX($A:$AG,0,MATCH(A10,$1:$1,0)),0),MATCH("STATUS",$1:$1,0))[/TD]
[/TR]
[TR]
[TH]C13[/TH]
[TD="align: left"]=INDEX($A:$AG,IFERROR(MATCH(A11,$H:$H,0),MATCH(A11,$I:$I,0)),MATCH("STATUS",$1:$1,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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.
Click to expand...

Thank you, works like a charm! :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Index and match function spanning multiple columns and rows
Replies
2
Views
339
L8NightKebab
L
W
  • Question Question
Index/Match Duplicate Values With No Helper Cells
Replies
3
Views
298
Domenic
D
U
  • Question Question
Dynamic Macro for INDEX/MATCH Formula
Replies
1
Views
365
StephenCrump
S
D
  • Solved
Combine 3 different index-match functions but referencing 1 specific helper cell
Replies
7
Views
571
smozgur
smozgur
B
  • Question Question
Use INDIRECT combined with INDEX MATCH (multiple row criteria) to return values from specified sheet
Replies
4
Views
586
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,902
Messages
6,175,278
Members
452,629
Latest member
SahilPolekar

Share this page

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