it might be an easy formula to populate table 2 with the information on table 1 but i am currently struggling to use multiple VLOOKUP functions. Has anyone please support me on this? the formula goes on cell B20 is basically checks if on that date(B19) a user(A20) has anything(A1:A13)..
CodePudding user response：
INDEX /MATCH seems most logic to me for this:
=IFERROR(INDEX($A$1:$A$13,MATCH(1,($C$1:$C$13=$A20)*($B$1:$B$13=B$19),0)),"")
It indexes column A and shows value of the row where both criteria are true (1).
This returns the first match. If you want to show multiple matches then you should use a different approach, dependent on your Excel version.
CodePudding user response：
Another formula maybe simpler to Understand and manipulate:
=IFERROR(INDEX( $A$2:$A$14,MIN( IF(1*($B$2:$B$14=B$19)*($C$2:$C$14=$A20)=1,ROW($A$2:$A$14)-1,999))),"")
must be entered as array in first Cell (and after each edit), then copy-paste everywhere... (for array press ctrl shift enter instead of enter)
Also note that
- in 06/11 Julie has 2 conditions and in both formulas only one is returned!
- in 02/11 Toto is mistake in your example Raif
- in Rory's formula changing 2 to 1 will give you the 1st encounter and thus get the 1st condition of Julie in 06/11 (1.)