# lookup data on multiple columns



## nholagi (Dec 23, 2022)

hi everyone, i've been playing around with index/match/lookup but haven't been able to find a solution, hope to get some helps. below is just a small sample of my huge table. thanks

table1 is my source table with individual name on each row by date(this go down by thousand of rows).
table 2 is my main table that i want to return a value if it match date and name from table 1.
i originally used vlookup and endedup missing all the data since it only returns the first match value
i'm looking for a function that will look at both columns on both tables and return either 1 or 0 on result column.
i appreciate your help!


*table 1**table 2*Datelast nameDatelast nameresult*09/01/22*​*Smith**09/01/22*​*Luo**1*​09/01/22​Toledo*09/01/22*​*Smith**1*​*09/01/22*​*Luo*09/01/22​Tran0​09/05/22​Smith09/02/22​Kani0​*09/05/22*​*Toledo*09/03/22​Luo0​09/10/22​Uro09/04/22​Tran0​*09/05/22*​*Toledo**1*​09/05/22​Uro0​09/10/22​Kani0​


----------



## Sufiyan97 (Dec 23, 2022)

What are 1 and 0s?


----------



## Fluff (Dec 23, 2022)

How about
Fluff.xlsmABCDEF1table 1table 22Datelast nameDatelast nameresult301/09/2022Smith01/09/2022Luo1401/09/2022Toledo01/09/2022Smith1501/09/2022Luo01/09/2022Tran0605/09/2022Smith02/09/2022Kani0705/09/2022Toledo03/09/2022Luo0810/09/2022Uro04/09/2022Tran0905/09/2022Toledo11005/09/2022Uro01110/09/2022Kani0MainCell FormulasRangeFormulaF3:F11F3=SIGN(COUNTIFS(A:A,D3,B:B,E3))


----------



## nholagi (Dec 29, 2022)

Sufiyan97 said:


> What are 1 and 0s?


it's just an indication that whether or not the person with the specific date appears on table 1


----------



## Sufiyan97 (Dec 29, 2022)

nholagi said:


> it's just an indication that whether or not the person with the specific date appears on table 1



Have you tried formula in post #3 by Fluff?


----------



## nholagi (Dec 30, 2022)

Fluff said:


> How about
> Fluff.xlsmABCDEF1table 1table 22Datelast nameDatelast nameresult301/09/2022Smith01/09/2022Luo1401/09/2022Toledo01/09/2022Smith1501/09/2022Luo01/09/2022Tran0605/09/2022Smith02/09/2022Kani0705/09/2022Toledo03/09/2022Luo0810/09/2022Uro04/09/2022Tran0905/09/2022Toledo11005/09/2022Uro01110/09/2022Kani0MainCell FormulasRangeFormulaF3:F11F3=SIGN(COUNTIFS(A:A,D3,B:B,E3))


yay it works! just curious, when i try just =COUNTIFS(A:A,D3,B:B,E3) it works as well, so does it matter with the SIGN?

also, could you help find a new function? i need to add another table (table 3). This table is basically looks at table 1 and 2 and returns rows of Date and last name if it's appearing on both tables (in reality i need to look at alot more tables but if i can get this function i can modify to look at all of them )


----------



## Fluff (Dec 30, 2022)

If you just use the countifs & something appears 3 times then the formul will return 3, but using Sign the formula will only return 1


----------

