Ask Your Question
0

vlookup , merge cells, 2 columns

asked 2017-09-13 22:13:10 +0100

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 flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2017-09-13 23:56:36 +0100

Jim K gravatar image

updated 2017-09-18 22:23:43 +0100

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"
edit flag offensive delete link more

Comments

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

dimzev gravatar imagedimzev ( 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?

Jim K gravatar imageJim K ( 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

can you help me please?

dimzev gravatar imagedimzev ( 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?

Jim K gravatar imageJim K ( 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!!!

dimzev gravatar imagedimzev ( 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.

Jim K gravatar imageJim K ( 2017-09-18 22:31:12 +0100 )edit

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

dimzev gravatar imagedimzev ( 2017-09-20 07:06:39 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-09-13 22:13:10 +0100

Seen: 49 times

Last updated: Sep 18