Multiple table array

Melaccio

New Member
Joined
May 21, 2019
Messages
13
Hello All,

I am trying to use an excel formula that calculates for me the match of two columns and show me the results.
If I want to calculate one column I am using this formula: =IF(ISERROR(VLOOKUP(E18,A18:A22,1,0))=TRUE,"",E18).

It's working really good, but it's seems impossible if i want to integrate a new column.
Can some one help me please? Below I am posting an example:

<style><!--table {mso-displayed-decimal-separator:"\,"; mso-displayed-thousand-separator:"\.";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style>[TABLE="width: 696"]
<!--StartFragment--> <colgroup><col width="87" span="8" style="width:65pt"> </colgroup><tbody>[TR]
[TD="width: 87"][TABLE="width: 435"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD="colspan: 2"] Table array 1[/TD]
[TD="colspan: 2"] Table array 2[/TD]
[TD]Lookup values[/TD]
[/TR]
[TR]
[TD]subject 1[/TD]
[TD][/TD]
[TD]subject a[/TD]
[TD][/TD]
[TD]subject n[/TD]
[/TR]
[TR]
[TD]subject 2[/TD]
[TD][/TD]
[TD]subcjet b[/TD]
[TD][/TD]
[TD]subject 1[/TD]
[/TR]
[TR]
[TD]subject 3[/TD]
[TD][/TD]
[TD]subject c[/TD]
[TD][/TD]
[TD]subject e[/TD]
[/TR]
[TR]
[TD]subject 4[/TD]
[TD][/TD]
[TD]subject d[/TD]
[TD][/TD]
[TD]subject q[/TD]
[/TR]
[TR]
[TD]subject 5
[/TD]
[TD][/TD]
[TD]subject e[/TD]
[TD][/TD]
[TD]subject 2[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD="width: 87"]


[/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[/TR]
</tbody>[/TABLE]
Thank you in advance.:)
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Hi & welcome to MrExcel.
how about


Excel 2013/2016
ABCDEF
1Table array 1Table array 2Lookup values
2subject 1subject asubject n 
3subject 2subcjet bsubject 1subject 1
4subject 3subject csubject esubject e
5subject 4subject dsubject q
6subject 5subject esubject 2subject 2
Sheet1
Cell Formulas
RangeFormula
F2=IF(ISERROR(VLOOKUP(E2,$A$2:$A$6,1,0)),IF(ISERROR(VLOOKUP(E2,$C$2:$C$6,1,0)),"",E2),E2)
 
Upvote 0
Hi & welcome to MrExcel.
how about

Excel 2013/2016
ABCDEF
Table array 1Table array 2Lookup values
subject 1subject asubject n
subject 2subcjet bsubject 1subject 1
subject 3subject csubject esubject e
subject 4subject dsubject q
subject 5subject esubject 2subject 2

<tbody>
[TD="align: center"]1[/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: center"]3[/TD]

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

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

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

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

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

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

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

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

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

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

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

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=IF(ISERROR(VLOOKUP(E2,$A$2:$A$6,1,0)),IF(ISERROR(VLOOKUP(E2,$C$2:$C$6,1,0)),"",E2),E2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Wow! Amazing! :eeek:

Thank you a lot :)

But if I want to integrate in the formula an automatic sort by?
I would like that it will show me in the same column the full cell from the first one, for example:

[TABLE="class: cms_table, width: 435"]
<tbody>[TR]
[TD="colspan: 2"][TABLE="width: 609"]
<tbody>[TR]
[TD="colspan: 2"]table array 1[/TD]
[TD="colspan: 2"]table array 2[/TD]
[TD="colspan: 2"]Lookup values[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]subject 1[/TD]
[TD][/TD]
[TD]subject a[/TD]
[TD][/TD]
[TD]subject n[/TD]
[TD][/TD]
[TD]subject 1[/TD]
[/TR]
[TR]
[TD]subject 2[/TD]
[TD][/TD]
[TD]subcjet b[/TD]
[TD][/TD]
[TD]subject 1[/TD]
[TD][/TD]
[TD]subject e[/TD]
[/TR]
[TR]
[TD]subject 3[/TD]
[TD][/TD]
[TD]subject c[/TD]
[TD][/TD]
[TD]subject e[/TD]
[TD][/TD]
[TD]subject 2[/TD]
[/TR]
[TR]
[TD]subject 4[/TD]
[TD][/TD]
[TD]subject d[/TD]
[TD][/TD]
[TD]subject q[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]subject 5[/TD]
[TD][/TD]
[TD]subject e[/TD]
[TD][/TD]
[TD]subject 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

<table cellpadding="2.5px" rules="all" verdana,="" arial,="" tahoma,="" calibri,="" geneva,="" sans-serif;="" border:="" 1px="" solid="" rgb(187,="" 187,="" 187);="" border-collapse:="" collapse;"="" style="width: " width=""><colgroup><col><col><col><col><col><col><col></colgroup><thead></thead><tbody></tbody></table>
I usually use a formula in the other column that will make order, like this one:

=IFERROR(INDEX($B$2:$B$1000;AGGREGATE(15;6;RIF.RIGA($A$2:$A$1000)-1/($B$2:$B$1000<>"");ROW($A1)));"")

Do you may know if it is possible to integrate this formula in the one that you showed me?
:confused:

Thank you again.
<style><!--table {mso-displayed-decimal-separator:"\,"; mso-displayed-thousand-separator:"\.";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {color:#222222; font-size:13.0pt; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0;}--></style>
 
Upvote 0
You could use this in col G
=IFERROR(INDEX($F$2:$F$6,AGGREGATE(15,6,(ROW($F$2:$F$6)-ROW($F$2)+1)/($F$2:$F$6<>""),ROWS($A$1:$A1))),"")
 
Upvote 0
You could use this in col G
=IFERROR(INDEX($F$2:$F$6,AGGREGATE(15,6,(ROW($F$2:$F$6)-ROW($F$2)+1)/($F$2:$F$6<>""),ROWS($A$1:$A1))),"")

Thank you again Fluff.

I tried this solution, but I was wondering if there is an option to integrate this formula in the column E with the result of the match, so I do not need to hide the column E to show an order result in the column G.
 
Upvote 0
There is probably a way to do it, however I have no idea how.
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,194
Members
452,616
Latest member
intern444

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