 # Vlookup , merge cells, 2 columns

I have:
on sheet1 5 columns A, B (empty cells) , C, D ,E
on sheet2 3 columns Α , Β, C ,D

i want :
when from sheet1 A1036 AND C1036 matches with A205 AND C205
write to sheet1 B1036 the value from sheet2 D205

**i i can do this if merge A&C columns but i ask if i can do this with NO merge columns

Enter into B1036:

``````=INDEX(Sheet2.D:D,MATCH(A1036&C1036,A:A&C:C,0),0)
``````

The combination of INDEX and MATCH is often used as a more powerful VLOOKUP.

EDIT:

For your example, put this in B1 and then fill down.

``````=IFNA(INDEX(Sheet2.D:D,MATCH(A1&C1,Sheet2.A:A&Sheet2.C:C,0)),"")
``````

The result is B1=, B2=30, B3=.

EDIT 2:

Explanation:

• IFNA - if resulting value is #N/A, then display “” (an empty string).
• INDEX - gets the value in Sheet 2 column D, at the row found by MATCH.
• MATCH - gets the row number where cells in Sheet 2 column A & C match Sheet 1 cells A1 & C1.
• & means concatenation, so “flag” & “yellow” = “flagyellow”

I think i was not clear before …
i want for every couple of cells from Sheet1 A and C columns (for example A1,C1…A10123, C10123) if matches with every couple from Sheet2 A and C columns …
then fill the corresponding cell from B column Sheet1 the value from D column

for example :
Sheet1
A1=dog A2-flag A3=cat
B1 = , B2= , B3=
C1=brown , C2= yellow ,C3=white

Sheet2
A1=sea , A2=flag , A3=flag

C1=yellow , C2=red ,C3=yellow
D1= 10, D2=20, D3=30
B1= , B2= ,B3=30

@dimzev: I set up the example you suggested, but I do not understand. Which values are being matched? For the third row, cat and white (Sheet 1 A3 and C3) are not the same as flag and yellow (sheet 2 A3 and C3), nor is cat found anywhere on sheet 2. So why would B3 be 30? Maybe B2 should be 30, to match flag and yellow?

# look my friend careful what i mean with example: sheet1 A2&C2 (flagyellow)

shett2 A3=C3 (flagyellow)

then must write on sheet1 , B2 cell the value from D3 (=30)
sheet1:
A1=dog A2-flag A3=cat
B1=(empty cell) B2=30 B3=(empty cell)
C1=brown , C2= yellow ,C3=white
sheet2:
A1=sea , A2=flag , A3=flag
C1=yellow , C2=red ,C3=yellow
D1= 10, D2=20, D3=30