CODEWITHSUNDEEP
excelexcel-formulavba
newusercitrix
newusercitrix

Reputation: 35

Some VLOOKUP, HLOOKUP, MATCH and INDEX combination which can pull the data from tables in different format into one summary sheet

I want some VLOOKUP, HLOOKUP, MATCH and INDEX combination which can pull the data from tables in different format into one summary sheet. One of the tables is in this format:

Name Date Scores
Sam 1 jan 23
Aron 1 jan 27
Alicia 1 jan 21
Sam 2 jan 24
Aron 2 jan 26
Alicia 2 jan 29
Sam 3 jan 20
Aron 3 jan 20
Alicia 3 jan 21

Other table is in this format:

name 1 mar 2 mar 3 mar 4 mar 5 mar
Sam P A P P P
Aron P P P A P
Alicia P A P P P

I want to create a summary sheet where I can pull the data from both the sheets accordingly like this:

Name Aron

Date Attendance Marks
1 jan P 27
2 jan P 26
3 jan P 20

I figured out the attendance sheet. I am using VLOOKUP and MATCH combination. However, I am experiencing issues pulling out the data from the first table. I can't figure out what formula to use.

Upvotes: 0

Views: 308

Answers (2)

matro
matro

Reputation: 29

Is there a specific reason why there 2 tables used ?

If not, try using something like this:

Name    Date    Scores  Attendance
Sam     1 jan   23      P
Aron    1 jan   27      P 
Alicia  1 jan   21      P
Sam     2 jan   24      A
Aron    2 jan   26      P
Alicia  2 jan   29      A
Sam     3 jan   20      P
Aron    3 jan   20      P
Alicia  3 jan   21      P

and then create Pivot Table to get desired result ?

Let me know, if that works for you.

Thanks.

M.

Upvotes: 0

user3616725
user3616725

Reputation: 3655

Read up on the SUMPRODUCT() function - Here is a good explanation

to summarise your problem you want to find out:

  1. which cells in column A of the Scores sheet have text "Aron"

    --> IF(A2:A20!`Scores`="Aron")

  2. which cells in column B of the Scores sheet contain date the same as in cell A4!Summary

    --> IF(B2:B20!`Scores`=A4!Summary)

  3. where 1. IS TRUE and 2. IS TRUE, get the cell value in corresponding row of column C

    --> =SUMPRODUCT(IF(A2:A20!`Scores`="Aron",1,0)*IF(B2:B20!`Scores`=A4!Summary,1,0)*C2:C10!`Scores`)

BE CAREFUL if it is possible that more than one row matches. This will return the SUM of the matching values

ALSO: if you used google Sheets instead you could simply use the FILTER() function

Upvotes: 0

Related Questions