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:

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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>
 
Upvote 0
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),"")
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,203
Members
452,617
Latest member
Narendra Babu D

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top