Vlookup , merge cells, 2 columns

[dimzev]

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

[jimk]

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”

[dimzev]

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
the answer isin Sheet1
B1= , B2= ,B3=30

[jimk]

@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?

[dimzev]

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

can you help me please?

[jimk]

@dimzev: Good, you corrected the example. Yes, that’s exactly what the second formula does. B2 results in 30 when the formula is filled down, as stated in my answer. Did you try it?

[dimzev]

YES my friend !!! you have right !!!
i put =IFNA(INDEX(Sheet2.D1:D3,MATCH(A1&C1,Sheet2.A1:A3&Sheet2.C1:C3,0)),"")
Because if i put D:D ,A:A, C:C my system collapse and dont work …
Thank you very much my friend .
Now must understand the formula you gave me (INDEX , MATCH) because i dont understand how exactly work. If you help me I would appreciate…
Τhank you!!!

[jimk]

@dimzev: See edited answer for an explanation. Good idea to use D1:D3. That must be needed with large amounts of data.

[dimzev]

Thank you very much my friend for your help !!!