Multiple table array

[Melaccio]

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-ignoreadding; 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 array 1 Table array 2 Lookup values subject 1 subject a subject n subject 2 subcjet b subject 1 subject 3 subject c subject e subject 4 subject d subject q subject 5 subject e subject 2 <colgroup><col span="5"></colgroup><tbody> </tbody>

<!--StartFragment--> <colgroup><col width="87" span="8" style="width:65pt"> </colgroup><tbody>
</tbody>

Thank you in advance.

 

[Fluff]

Hi & welcome to MrExcel.
how about

Excel 2013/2016
	A	B	C	D	E	F
1	Table array 1		Table array 2		Lookup values
2	subject 1		subject a		subject n
3	subject 2		subcjet b		subject 1	subject 1
4	subject 3		subject c		subject e	subject e
5	subject 4		subject d		subject q
6	subject 5		subject e		subject 2	subject 2
Sheet1

Cell Formulas
Range	Formula
F2	=IF(ISERROR(VLOOKUP(E2,$A$2:$A$6,1,0)),IF(ISERROR(VLOOKUP(E2,$C$2:$C$6,1,0)),"",E2),E2)

 

[Melaccio]

Hi & welcome to MrExcel.
how about

Excel 2013/2016

	A	B	C	D	E	F
1	Table array 1		Table array 2		Lookup values
2	subject 1		subject a		subject n
3	subject 2		subcjet b		subject 1	subject 1
4	subject 3		subject c		subject e	subject e
5	subject 4		subject d		subject q
6	subject 5		subject e		subject 2	subject 2

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula F2 =IF(ISERROR(VLOOKUP(E2,$A$2:$A$6,1,0)),IF(ISERROR(VLOOKUP(E2,$C$2:$C$6,1,0)),"",E2),E2) <tbody> </tbody>

<tbody>
</tbody>

Wow! Amazing!

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 array 1 table array 2 Lookup values subject 1 subject a subject n subject 1 subject 2 subcjet b subject 1 subject e subject 3 subject c subject e subject 2 subject 4 subject d subject q subject 5 subject e subject 2 <tbody> </tbody>

<tbody>
</tbody>

<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?

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-ignoreadding; 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>

 

[Fluff]

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))),"")

 

[Melaccio]

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.

 

[Fluff]

There is probably a way to do it, however I have no idea how.