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?

 

[MikeyZ]

Can you use an Nested IF and VLOOKUP together?
Basically if this VLOOKUP = 1,False, and the other one -2,false, then ...3,false.

Something like this:
=IF(VLOOKUP(a2,Sheet1!$A$2:$D$500, 1,false)=A2, AND(VLOOKUP(a2,Sheet1!$A$2:$D$500,2,false)=B2),VLOOKUP(a2,Sheet1!$A$2:$D$500, 3,false)," ")

I'm not sure how to place the parenthesis().

 

[Marcelo Branco]

Try this Array-formula

=INDEX(Sheet1!$C$2:$C$500,MATCH(1,IF(Sheet1!$A$2:$A$500=A2,IF(Sheet1!$B$2:$B$500=B2,1)),0))

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

HTH

M.

 

[MikeyZ]

I understand the formula to process as follows.
First the INDEX Array covers the first sheet.The Match is targeting Sheet2 as does the last IF statement.

 

[Marcelo Branco]

I'm afraid that i'm not following you. The formula worked or not?

M.

 

[MikeyZ]

Formula did not work.
IT doesn't target Sheet2 .

 

[MikeyZ]

Let's start over. Data on Sheet 1

A B C
1 0620401 010 Formula goes here
2 0620401 030
3 0710501 040
4

Sheet2
A B C
1 0710501 030 Grinding
2 0642401 010 Plating
3 0642401 040 Clean
4


I want to INDEX($A$2:$A400)
Match up on SHEET2, then if on Sheet2 the A2 matches, and B2 matches, I want the data in C2 to be placed in the cell in C2.

 

[Marcelo Branco]

Change the references from Sheet1 to Sheet2

=INDEX(Sheet2!$C$2:$C$500,MATCH(1,IF(Sheet2!$A$2:$A$500=A2,IF(Sheet2!$B$2:$B$500=B2,1)),0))

Ctrl+Shift+Enter

M.

 

[Marcelo Branco]

Sorry, accordingly with your last post your data start in row 1, so

=INDEX(Sheet2!$C$1:$C$500,MATCH(1,IF(Sheet2!$A$1:$A$500=A1,IF(Sheet2!$B$1:$B$500=B1,1)),0))

Ctrl+Shift+Enter

 

[MikeyZ]

NOt making sense. Sorry.
In Cell C1 on Sheet1 I enter the Fx.
the 2 cells on Sheet1 of importance is A2 and B2.

I want to go to Sheet2, if the value in A2 and B2 match what's on Sheet1 (anywhere) pull the data from that cell in col "C" on sheet2 and place it in the cell in C2 in sheet1.

I don't that happening. Yet.