Multiple table array

M

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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
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
Fluff said:
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]
Click to expand...

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
Fluff said:
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))),"")
Click to expand...

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
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
645
smozgur
smozgur
M
  • Question Question
"Sort by" Formula
2
Replies
11
Views
2K
Marcelo Branco
M
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
M
  • Question Question
Excel formula combine in different sheet
Replies
8
Views
965
Marcelo Branco
M
C
  • Question Question
IF Condition help (I believe)
Replies
3
Views
1K
Joe4
Joe4

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

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