# vlookup , merge cells, 2 columns [closed]

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

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2020-08-07 22:43:37.054025

Sort by » oldest newest most voted

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"
more

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

( 2017-09-14 12:25:29 +0100 )edit

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

( 2017-09-16 22:14:13 +0100 )edit

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

( 2017-09-17 15:57:57 +0100 )edit

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

( 2017-09-18 15:24:31 +0100 )edit

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!!!

( 2017-09-18 19:05:37 +0100 )edit

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

( 2017-09-18 22:31:12 +0100 )edit

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

( 2017-09-20 07:06:39 +0100 )edit