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

 

[DanteAmor]

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

for that part, try this:

<b>Sheet1</b><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >W1</td><td >=INDEX(Sheet2!L1:L10,SUMPRODUCT((Sheet2!A1:A10=E1)*(Sheet2!E1:E10=J1)*ROW(Sheet2!L1:L10)))</td></tr></table></td></tr></table>

 

[Special-K99]

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

 

[Tianorth]

Brilliant it works
Definitely would not have got there!
Thank you

 

[Tianorth]

Hi DanteAmor

Having just check a few I have come across a few that do not match the results.
In (Tab1) issue = G

In (Tab2) issue = F
But it has copied column L

What I was trying to get it to do, is if the issue in bothtabs are the same to copy column L.

This seems to copy column L if the first criteria is met.

I require the first criteria (part number) and secondcriteria (issue) to match, if they do then copy value of column (tab2)L

Sorry for having to review this.

Many thanks
Tianorth

 

[DanteAmor]

Sorry, I do not understand the requirement now. I attach my evidence file to you to review the data.
About the same file explains what data you have and what you expect of result.

https://www.dropbox.com/s/e7zxgkcyidwuf0w/look at 2 non adjacent cells.xlsx?dl=0

You could upload of file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[Tianorth]

Sorry for the delay in getting back.

In a row there is a part number (column E) and issue (column J)

In another tab in column A for part number and same row column E for the issue, if these are the same then display column L (of the same row) in Tab1 column W.



The formula used is copying column L even if the issues are not the same (column L is issue status eg WIP, APP, STP, REL).


Hope this makes sense.
Tianorth

 

[DanteAmor]

Do not worry.
Did you try the file I uploaded?
But to understand what you need you could give an example with real data.
You can upload the example to dropbox.

 

[Tianorth]

Not able to view file (against company policy) so trying to view on phone.

Created a file


https://1drv.ms/x/s!AoIna7Gq4-l4g0G4kqnpVdUTExhR

Many thanks
Tianorth

 

[DanteAmor]

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


Create the following information on each sheet

<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>Sheet1</b></td></tr></table><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:78.89px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >E</td><td >J</td><td >W</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td >first</td><td >second</td><td >third</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td >some</td><td >data</td><td >Hello</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >W1</td><td >=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)))</td></tr><tr><td >W2</td><td >=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)))</td></tr></table></td></tr></table>

<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>Sheet2</b></td></tr></table><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >E</td><td >L</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >first</td><td >second</td><td >third</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >some</td><td >data</td><td >Hello</td></tr></table>