0

I am referring to below my google spreadsheet

https://docs.google.com/spreadsheets/d/1dCfShenhV2j98q5wkOXMeyWj9tlMZbaBgBqB2vAPdHo/edit?usp=sharing

I am looking to update H,I and J columns using vlook formula in way that it should match both name and date values in my data range, which in A,B and C columns

Here is the issue I am facing with normal vlookup is that I can check only name.It is ignoring the date and updating the vlooked up data on all date column.

Eg: Alpha and date 20141120 value is 10, it should fill only H3, but it is updating, H3 I3 and J3 with value 10

I really appreciate your answer on this problem!!!

Community
  • 1
  • 1
acr
  • 1,674
  • 11
  • 45
  • 77

3 Answers3

1

you can use this formula of index and match: 

=IFERROR(INDEX($A:$C,MATCH(1,($A:$A=$G3)*($B:$B=H$2),0),3),"")


paste it in the first cell of your table H3, and drag and fill to the right and then select the entire row and fill down till end. it should work.
if error(();"") : you will get empty cells if there is no match.
this is an array formula, so press ctrl+shift+enter to calculate the formula

UPDATE: here is [the example sheet downloadable from here}(https://www.dropbox.com/s/clqxsj5j4bdk27b/indexmatch.xlsx?dl=0)

Marcel
  • 2,764
  • 1
  • 24
  • 40
  • it works for me, maybe you are not pressing ctrl+shift+enter or your cell references are different from the ones in the formula. – Marcel Dec 01 '14 at 19:55
  • worked after cleaned up the tab and re-created..thanks for the quick help – acr Dec 01 '14 at 20:20
  • If I want to show another value instead of empty cell for no match, how can I edit it ? I want to use value 100, and when I am editing the last portion "H2=M3"), getting error circular refernce – acr Dec 01 '14 at 20:38
  • what is H2=M3 about? where do you put it? you can put **100** inside that **" "** , so replace it with **"100"**. the you will get 100 instead of empty cells. – Marcel Dec 01 '14 at 20:48
  • I trying to add another formula, instead of showing it as blank, gor example, get the dara from cell M2 into H2 (H2=M2) – acr Dec 01 '14 at 21:38
  • write your complete formula here and let us see what it is like. – Marcel Dec 01 '14 at 21:40
  • Can you please let me know why this same formula is not working here https://drive.google.com/file/d/0B5W4CgZKsARFUlQ2c0dfVENhaWc/view?usp=sharing – acr Dec 02 '14 at 14:07
  • 1
    the reason is that you are refering to data!B$2, and this is wrong. you should refer to data!B$1, because this cell contains your date. in my example, H$2 refers to the date, because it is in the second row, but in your excel file the dates are in the first row. so change it to data!B$1 and then ctrl+shft+enter, then drag and fill to the right then drag the entire row and fill down till end. tell me if it works. – Marcel Dec 02 '14 at 14:32
  • Thanks, I have noticed it now and changed. Now it is working but it take some time to update all cells. Is that excepted behavior with this formula or can I do something to calculate it fast – acr Dec 02 '14 at 14:39
  • no it is normal, because you are using array formula and data retrieval from another sheet. – Marcel Dec 02 '14 at 14:52
0

Basically you need to concatenate the results, then use a VLOOKUP on that.

I.e. insert a column between B and C, with formula "=CONCATENATE(A2,B2)"

In the range you want to update, use the column and row headings for you lookup "=VLOOKUP(CONCATENATE($g3,h$2),$c$1:$d$3,2,false)"

mlinth
  • 2,968
  • 6
  • 30
  • 30
0

You want to perform a Multiple Lookup (see this). As indicated there, enter

=IFERROR(LOOKUP(2,1/($A$1:$A$3=$G3)/($B$1:$B$3=H$2),$C$1:$C$3),"")

in H3. Copy into H3:J5.

This avoids array formulas.

Community
  • 1
  • 1
sancho.s ReinstateMonicaCellio
  • 14,708
  • 20
  • 93
  • 185