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

Tianorth

New Member
Joined
Apr 11, 2019
Messages
14
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 2
nd
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
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,932
Office Version
2007
Platform
Windows
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

Well-known Member
Joined
Nov 7, 2006
Messages
8,328
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

New Member
Joined
Apr 11, 2019
Messages
14
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

Well-known Member
Joined
Dec 3, 2018
Messages
8,932
Office Version
2007
Platform
Windows
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

New Member
Joined
Apr 11, 2019
Messages
14
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
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,932
Office Version
2007
Platform
Windows
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.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,932
Office Version
2007
Platform
Windows
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>
 

Forum statistics

Threads
1,082,504
Messages
5,365,962
Members
400,864
Latest member
RobynP51

Some videos you may like

This Week's Hot Topics

Top