Excel formula(s) look at 2 non adjacent cells in same row, see if in another tab

[Tianorth]

Hi

I require a little help (scrub that change little for a lot of) please, trying to write a formula where is looks at 2 non adjacent cells in one tab, and looks in another tab to see if it can find the same info, if it does then to put from the 2nd tab another cell value.


So in (tab1)W1
IF (tab1)E1 = (tab2)A:A and (tab1)J1 = (tab2)E:E then put(tab2)L:L


Looked at using VLookup but only 1 value to look at




The second part is a nice to have!
(tab2) only contains latest issues
Column (tab1)J:J and (tab2)E:E contain issue values

To go in (tab1)X1

IF (tab1)J:J and (tab2)E:E are not equal then “Lower issue than latest” or “Higher issue than latest” or “Latest”

The issue are single numeric issue àsingle digit alpha with double numeric (egB03) à single digit alpha à double digit alpha with single numeric (eg AB3) àdouble digit alpha

Numeric issue are lower than Alpha issues
Single digit alpha with double numeric come in between issue A & B (eg A, B01, B02, B03, B04, B05, B)
Double digit alpha are similar (eg Z, AA1, AA2, AA3, AB,AC)
Alpha issues with numeric will not always be used.
Not all Alpha issues with numeric will be used (max of 5)


Hope this makes sense

Many thanks
Tianorth

 

[Tianorth]

Think this should work, havent done this for some time

=IFERROR(INDEX(Tab2!$L$1:$L$1000,AGGREGATE(15,6,ROW(Tab2!$L$1:$L$1000)/((Tab2!$A$1:$A$1000=E1)*(Tab2!E$1:E$1000=J1)),ROWS(A$1:A1))-(1-1),1),"")

Hi Special-K99

I sort of now understand your formula, still some bits of the formula I'm not sure about, need to check the data, but it seems to work.

Thank you
Tianorth

 

[Tianorth]

In the same way I can not see the file, only dropbox.


Create the following information on each sheet

Sheet1

<tbody>
</tbody>

	A	E	J	W
	first	second	third
	some	data	Hello

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

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

</tbody>

Cell Formula W1 =INDEX(Sheet2!$L$1:$L$10,SUMPRODUCT((Sheet2!$A$1:$A$10=E1)*(Sheet2!$E$1:$E$10=J1)*ROW(Sheet2!$L$1:$L$10))) W2 =INDEX(Sheet2!$L$1:$L$10,SUMPRODUCT((Sheet2!$A$1:$A$10=E2)*(Sheet2!$E$1:$E$10=J2)*ROW(Sheet2!$L$1:$L$10))) <tbody> </tbody>

<tbody>
</tbody>

Sheet2

<tbody>
</tbody>

	A	E	L
first	second	third
some	data	Hello

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

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

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

</tbody>

When using this formula the part where it is looking for J1 or J2 etc, it is not matching then it still puts cell L:L

If you add a 3rd line to the sheets where column E is different, then column W should not contain anything.

I added (NOT) (CORRECT) on sheet one
I added (NOT) (WRONG) (SHOULD BE BLANK) on sheet two

I got a result for 3rd row REL which is in previous data (no idea which row it is from)

When I put it in a new file I get SHOULD BE BLANK which is not correct.

As CORRECT an WRONG (Column J & E) do not match.

Many thanks
Tianorth

 

[DanteAmor]

Try this

=IFERROR(INDEX(Sheet2!$L$2:$L$10,SUMPRODUCT((Sheet2!$A$1:$A$10=E1)*(Sheet2!$E$1:$E$10=J1)*ROW(Sheet2!$L$1:$L$10))-1), "No match")

 

[Tianorth]

Sheet1 column E,J & L


[TABLE="width: 517"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"] First
[/TD]
[TD="width: 11, bgcolor: transparent"][/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"] Second
[/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 520, bgcolor: transparent"] No match
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] SOME
[/TD]
[TD="width: 11, bgcolor: transparent"][/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"] data
[/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 520, bgcolor: transparent"] Hello
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] Not
[/TD]
[TD="width: 11, bgcolor: transparent"][/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"] correct
[/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 520, bgcolor: transparent"] No match
[/TD]
[/TR]
</tbody>[/TABLE]



Sheet 1 with formula


[TABLE="width: 1037"]
<tbody>[TR]
[TD="width: 66, bgcolor: transparent"] First
[/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"] Second
[/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 1213, bgcolor: transparent"] =IFERROR(INDEX(Sheet2!$L$2:$L$10,SUMPRODUCT((Sheet2!$A$1:$A$10=E1)*(Sheet2!$E$1:$E$10=J1)*ROW(Sheet2!$L$1:$L$10))-1), "No match")
[/TD]
[/TR]
[TR]
[TD="width: 66, bgcolor: transparent"] SOME
[/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"] data
[/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 1213, bgcolor: transparent"] =IFERROR(INDEX(Sheet2!$L$2:$L$10,SUMPRODUCT((Sheet2!$A$1:$A$10=E2)*(Sheet2!$E$1:$E$10=J2)*ROW(Sheet2!$L$1:$L$10))-1), "No match")
[/TD]
[/TR]
[TR]
[TD="width: 66, bgcolor: transparent"] Not
[/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"] correct
[/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 1213, bgcolor: transparent"] =IFERROR(INDEX(Sheet2!$L$2:$L$10,SUMPRODUCT((Sheet2!$A$1:$A$10=E3)*(Sheet2!$E$1:$E$10=J3)*ROW(Sheet2!$L$1:$L$10))-1), "No match")
[/TD]
[/TR]
</tbody>[/TABLE]



Sheet2 columns A, E & L


[TABLE="width: 624"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"] First
[/TD]
[TD="width: 11, bgcolor: transparent"][/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 76, bgcolor: transparent"] Second
[/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 132, bgcolor: transparent"] Third
[/TD]
[TD="width: 417, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] SOME
[/TD]
[TD="width: 11, bgcolor: transparent"][/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 76, bgcolor: transparent"] data
[/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 132, bgcolor: transparent"] Hello
[/TD]
[TD="width: 417, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] Not
[/TD]
[TD="width: 11, bgcolor: transparent"][/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 9, bgcolor: transparent"][/TD]
[TD="width: 76, bgcolor: transparent"] wrong
[/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 549, bgcolor: transparent, colspan: 2"] Should be blank
[/TD]
[/TR]
</tbody>[/TABLE]


Icannot see anything wrong with the formula but the result is unexpected!

Many thanks
Tianorth

 

[DanteAmor]

I assumed you had a title in the row 1 on to sheet2, simply put your data on sheet2, starting in row 2