CODEWITHSUNDEEP
excelexcel-formulavlookup
Raif
Raif

Reputation: 27

VLOOKUP, or INDEX/MATCH?

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)..

Final Look

Upvotes: 1

Views: 241

Answers (2)

P.b
P.b

Reputation: 11415

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.

Upvotes: 1

Apostolos55
Apostolos55

Reputation: 580

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

  1. in 06/11 Julie has 2 conditions and in both formulas only one is returned!
  2. in 02/11 Toto is mistake in your example Raif
  3. 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.)

Upvotes: 1

Related Questions