Index match column and row

S

SAXON10

Board Regular
Joined
Jun 1, 2017
Messages
109
Hi,

I am trying to index match with three different table ranges. I am apply the following formula in

B16=IF(MATCH(A$15,$A$2:$B$2,0),VLOOKUP($A16,$A$3:$B$12,2,0)) the formula working fine but the problem is every time I have change table ranges. I

need one formula for whole table range.

Any help much appreciated.

Data Range: A2:K12

[TABLE="width: 756"]
<tbody>[TR]
[TD="colspan: 11"]DATA[/TD]
[/TR]
[TR]
[TD]TATA[/TD]
[TD]TEXT[/TD]
[TD][/TD]
[TD]FORCE[/TD]
[TD]TEXT[/TD]
[TD]TEXT1[/TD]
[TD][/TD]
[TD]HERO[/TD]
[TD]TEXT[/TD]
[TD]TEXT1[/TD]
[TD]TEXT2[/TD]
[/TR]
[TR]
[TD="align: right"]5848[/TD]
[TD]ORDER[/TD]
[TD][/TD]
[TD="align: right"]5848[/TD]
[TD]ORDER[/TD]
[TD]US[/TD]
[TD][/TD]
[TD="align: right"]5848[/TD]
[TD]ORDER[/TD]
[TD]US[/TD]
[TD]US[/TD]
[/TR]
[TR]
[TD="align: right"]6847[/TD]
[TD]NOT[/TD]
[TD][/TD]
[TD="align: right"]6847[/TD]
[TD]NOT[/TD]
[TD]UK[/TD]
[TD][/TD]
[TD="align: right"]6847[/TD]
[TD]ORDER[/TD]
[TD]UK[/TD]
[TD]UK[/TD]
[/TR]
[TR]
[TD="align: right"]68798[/TD]
[TD]ORDER[/TD]
[TD][/TD]
[TD="align: right"]68798[/TD]
[TD]ORDER[/TD]
[TD]CHINA[/TD]
[TD][/TD]
[TD="align: right"]68798[/TD]
[TD]ORDER[/TD]
[TD]CHINA[/TD]
[TD]CHINA[/TD]
[/TR]
[TR]
[TD="align: right"]4587[/TD]
[TD]ORDER[/TD]
[TD][/TD]
[TD="align: right"]4587[/TD]
[TD]ORDER[/TD]
[TD]NZ[/TD]
[TD][/TD]
[TD="align: right"]4587[/TD]
[TD]ORDER[/TD]
[TD]NZ[/TD]
[TD]NZ[/TD]
[/TR]
[TR]
[TD="align: right"]1112[/TD]
[TD]ORDER[/TD]
[TD][/TD]
[TD="align: right"]1112[/TD]
[TD]NOT[/TD]
[TD]AUS[/TD]
[TD][/TD]
[TD="align: right"]1112[/TD]
[TD]ORDER[/TD]
[TD]AUS[/TD]
[TD]AUS[/TD]
[/TR]
[TR]
[TD="align: right"]22235[/TD]
[TD]ORDER[/TD]
[TD][/TD]
[TD="align: right"]22235[/TD]
[TD]NOT[/TD]
[TD]EUR[/TD]
[TD][/TD]
[TD="align: right"]22235[/TD]
[TD]ORDER[/TD]
[TD]EUR[/TD]
[TD]EUR[/TD]
[/TR]
[TR]
[TD="align: right"]456[/TD]
[TD]ORDER[/TD]
[TD][/TD]
[TD="align: right"]456[/TD]
[TD]CANCEL[/TD]
[TD]RUS[/TD]
[TD][/TD]
[TD="align: right"]456[/TD]
[TD]ORDER[/TD]
[TD]RUS[/TD]
[TD]RUS[/TD]
[/TR]
[TR]
[TD="align: right"]487[/TD]
[TD]ORDER[/TD]
[TD][/TD]
[TD="align: right"]487[/TD]
[TD]ORDER[/TD]
[TD]ERA[/TD]
[TD][/TD]
[TD="align: right"]487[/TD]
[TD]REJ[/TD]
[TD]ERA[/TD]
[TD]ERA[/TD]
[/TR]
[TR]
[TD="align: right"]587[/TD]
[TD]NOT[/TD]
[TD][/TD]
[TD="align: right"]587[/TD]
[TD]NOT[/TD]
[TD]NIPA[/TD]
[TD][/TD]
[TD="align: right"]587[/TD]
[TD]PENDING[/TD]
[TD]NIPA[/TD]
[TD]NIPA[/TD]
[/TR]
[TR]
[TD="align: right"]657[/TD]
[TD]ORDER[/TD]
[TD][/TD]
[TD="align: right"]657[/TD]
[TD]ORDER[/TD]
[TD]BAN[/TD]
[TD][/TD]
[TD="align: right"]657[/TD]
[TD]ORDER[/TD]
[TD]BAN[/TD]
[TD]BAN[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]FORMULA REQUIRED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TATA[/TD]
[TD]TEXT[/TD]
[TD]FORCE[/TD]
[TD]TEXT[/TD]
[TD]HERO[/TD]
[TD]TEXT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6847[/TD]
[TD]NOT[/TD]
[TD="align: right"]1112[/TD]
[TD]NOT[/TD]
[TD="align: right"]456[/TD]
[TD]ORDER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]68798[/TD]
[TD]ORDER[/TD]
[TD="align: right"]22235[/TD]
[TD]NOT[/TD]
[TD="align: right"]487[/TD]
[TD]REJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4587[/TD]
[TD]ORDER[/TD]
[TD="align: right"]456[/TD]
[TD]CANCEL[/TD]
[TD="align: right"]587[/TD]
[TD]PENDING[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1112[/TD]
[TD]ORDER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


https://www.dropbox.com/s/379m87ppijo31b2/INDEX MATCH.xlsx?dl=0
 
Last edited:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
in B16:
<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B16</th><td style="text-align:left">=INDEX(<font color="Blue">(<font color="Red">$A$2:$B$11,$D$2:$F$11,$H$2:$K$11</font>),MATCH(<font color="Red">A16,$A$2:$A$11,0</font>),2,1+QUOTIENT(<font color="Red">COLUMNS(<font color="Green">$B16:B16</font>)-1,2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

then copy it into the other green cells



Excel 2013/2016
ABCDEFGHIJK
1TATATEXTFORCETEXTTEXT1HEROTEXTTEXT1TEXT2
25848ORDER5848ORDERUS5848ORDERUSUS
36847NOT6847NOTUK6847ORDERUKUK
468798ORDER68798ORDERCHINA68798ORDERCHINACHINA
54587ORDER4587ORDERNZ4587ORDERNZNZ
61112ORDER1112NOTAUS1112ORDERAUSAUS
722235ORDER22235NOTEUR22235ORDEREUREUR
8456ORDER456CANCELRUS456ORDERRUSRUS
9487ORDER487ORDERERA487REJERAERA
10587NOT587NOTNIPA587PENDINGNIPANIPA
11657ORDER657ORDERBAN657ORDERBANBAN
12
13
14
15TATATEXTFORCETEXTHEROTEXT
166847NOT1112NOT456ORDER
1768798ORDER22235NOT487REJ
184587ORDER456CANCEL587PENDING
191112ORDER
Sheet4
 
Upvote 0
Quick question how do add the name range(A15) in the same formula. Please help me.


VBA Geek said:
in B16:
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] "]B16[/TH]
[TD="align: left"]=INDEX(($A$2:$B$11,$D$2:$F$11,$H$2:$K$11),MATCH(A16,$A$2:$A$11,0),2,1+QUOTIENT(COLUMNS($B16:B16)-1,2))[/TD]
[/TR]
</tbody>[/TABLE]



then copy it into the other green cells


Excel 2013/2016
ABCDEFGHIJK
TATATEXTFORCETEXTTEXT1HEROTEXTTEXT1TEXT2
ORDERORDERUSORDERUSUS
NOTNOTUKORDERUKUK
ORDERORDERCHINAORDERCHINACHINA
ORDERORDERNZORDERNZNZ
ORDERNOTAUSORDERAUSAUS
ORDERNOTEURORDEREUREUR
ORDERCANCELRUSORDERRUSRUS
ORDERORDERERAREJERAERA
NOTNOTNIPAPENDINGNIPANIPA
ORDERORDERBANORDERBANBAN
TATATEXTFORCETEXTHEROTEXT

<colgroup><col style="width: 25pxpx"><col><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: center"]2[/TD]
[TD="align: right"]5848[/TD]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[TD="align: right"][/TD]
[TD="align: right"]657[/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: 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]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/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: right"][/TD]

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

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

[TD="align: center"]16[/TD]
[TD="align: right"]6847[/TD]
[TD="bgcolor: #C6E0B4"]NOT[/TD]
[TD="align: right"]1112[/TD]
[TD="bgcolor: #C6E0B4"]NOT[/TD]
[TD="align: right"]456[/TD]
[TD="bgcolor: #C6E0B4"]ORDER[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]68798[/TD]
[TD="bgcolor: #C6E0B4"]ORDER[/TD]
[TD="align: right"]22235[/TD]
[TD="bgcolor: #C6E0B4"]NOT[/TD]
[TD="align: right"]487[/TD]
[TD="bgcolor: #C6E0B4"]REJ[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]4587[/TD]
[TD="bgcolor: #C6E0B4"]ORDER[/TD]
[TD="align: right"]456[/TD]
[TD="bgcolor: #C6E0B4"]CANCEL[/TD]
[TD="align: right"]587[/TD]
[TD="bgcolor: #C6E0B4"]PENDING[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]1112[/TD]
[TD="bgcolor: #C6E0B4"]ORDER[/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>
Sheet4​
Click to expand...
 
Upvote 0
Hi,

Yes you missing the following name range TATA, HERO and FORCE.(MATCH(FORCE)(A15,C15 AND E15)
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Index Match Error #REF
Replies
3
Views
728
Marcelo Branco
M

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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