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
1		first	second	third
2		some	data	Hello

<tbody>
</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
1
4	first	second	third
5	some	data	Hello

<tbody>
</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

First					Second		No match
SOME					data		Hello
Not					correct		No match

<tbody> </tbody>

Sheet 1 with formula

First					Second		=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")
SOME					data		=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")
Not					correct		=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")

<tbody> </tbody>

Sheet2 columns A, E & L

First

Second

Third

SOME

data

Hello

Not

wrong

Should be blank

<tbody> </tbody>

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