VLOOKUP or some other way

[MikeyZ]

I have a value in A2 = 0642001
in B2 = 010
The value in B2 is a release # and could change, but the A2 value won't.

On another sheet I have an Array of values in A and B.
There may be several values in A like 0642001 but several sifferent values in B that are adjacent to A, but different.
For example: A10 = 0642001 and B10 = 040.

I want to look at the value in A10 and B10, search thru sheet2 array (A2:D500) and if there's a match, pull the value in C10.

I know Vlookup works for one unique value, is there something that let's me look at 2 cells?

 

[Marcelo Branco]

Enter the Array-formula in C1 and copy down

M.

 

[Marcelo Branco]

Sorry, i'm not understanding what you need...
What is the expected result with data-sample in #7?

M.

 

[MikeyZ]

On sheet1 anter the following data

In A2 enter 123456
in B2 enter 010
in C2 we will enter our formula

 

[MikeyZ]

oops somehow I hit enter by mistake.

to continue.
On Sheet2 enter the following:

 

[MikeyZ]

Sorry. Start over.

On sheet1 enter the following
In A2 enter 123456
In B2 enter 010
In C3 will be our formula.


On Sheet2 enter the following:
In A2 enter 245689
In B2 enter 020
In C2 enter Plate

In A3 enter 123456
In B3 enter 010
In C3 enter "Plate". This will be the current process this P/N is undergoing.

In C3 of Sheet1, I need an Index formula to look at the data in A2 of (sheet1), then go to sheet2, search the Array for the match in A2 of sheet1. Then look at the data on sheet2, to match the same data in B2. If A2 matches somewhere on Sheet1 with that on sheet2, and the data in B2 is the same as that on sheet1, I want to pull the data in C3 of sheet2 over into sheet1 to C3.

In this case, I want to see the word Plate from sheet2, from B3 in c3 of sheet1.

Hope that's clearer.
Then go to Sheet2, look in the Array find a match for A2 in sheet1, Make sure the data in B2 of sheet2 matches that in sheet1

 

[Marcelo Branco]

Lets try again

Sheet2

A B C
<TABLE style="WIDTH: 149pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=199><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #c5d9f1; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=64>Value1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Value2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=71>Value3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>245689</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>020</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Plate</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>123456</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Plate</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>123456</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>030</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>sometext</TD></TR></TBODY></TABLE>


Sheet1

A B C
<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #c5d9f1; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20 width=64>Value1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>Value2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>Result</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>123456</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Plate</TD></TR></TBODY></TABLE>

Array-Formula in C2
=INDEX(Sheet2!$C$2:$C$4,MATCH(1,IF(Sheet2!$A$2:$A$4=A2,IF(Sheet2!$B$2:$B$4=B2,1)),0))

confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

For testing purposes change the Value2 in Sheet1 B2 to 030 and you get

A B C
<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #c5d9f1; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20 width=64>Value1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>Value2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>Result</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>123456</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>030</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>sometext</TD></TR></TBODY></TABLE>

HTH

M.

 

[MikeyZ]

i'll try it.I've come real close on my own, and I understand the flow of the formula. the one I have just isn't working yet.

I tried a small test on some sample data and it worked.
I'm not sure what's wrong.

Here's the one I created. The target sheets are named differently but the 3 columns on the Milestone sheet and the 2 on the main sheet (Daily) are all adjacent.

Here's mine:
=INDEX('Milestone Inventory'!$J$7:$J$155,MATCH('Daily Worksheet'!K710,IF('Milestone Inventory'!$I$7:$I$155='Daily Worksheet'!L710,'Milestone Inventory'!$H$7:$H$155),0))

I did do CSE.

 

[MikeyZ]

YIPEEE
I got it.
I had the wrong cell number in my formula.
Thanks for all your help.

Mike

 

[Marcelo Branco]

YIPEEE
I got it.
I had the wrong cell number in my formula.
Thanks for all your help.

Mike

You are welcome and tks for the feedback

M.

 

[MikeyZ]

After diligent work, I was able to create the following (which may or may not interest others):

=VLOOKUP(K2&L2,Sheet3!$A:$B,2,FALSE)

First, I didn't know you could use the "&" in a VLOOKUP formula and then target a different portion of an ARRAY to pull the info. I thought this was awesome.

I look at the two cells I wanted to match, then went to my other sheet, and it pulled the info from the column with the Process data mentioned thru-out the thread.