Multi-condition Excel lookup with 2 sheets?

Question

Here's how my tables look:

Sheet1
Name(Column)
USER(A)----Date(J)---(Empty-return results)(N)

Sheet2
Name(Column)
USER(A)--SEGM(C)--Date(F)

I need to return the results of SEGM, column C when the USER & Date information match (ie; johndoe & 10/1/08, johndoe & 11/1/08 = SEGM1, SEGM2 as results).

Problem is that Sheet1 has more rows (extra users) than Sheet2 has and using the logic provided in two answers begins returning incorrect results once a user in Sheet1 messes up the order.

Any suggestions on how to bypass? Tried VLOOKUP but it stops after the first record and doesn't get me the SEGM tied to date history.

Answer 1

is SEGM a number that you want to sum up or a text field that you just need to find and match?



edit-

never mind...this is a continuation of your last question



i used row 2 to row 99

you just need to change your range.

copy&paste this formula



=INDEX(Sheet2!$C$2:$C$99, MATCH(A2, IF(Sheet2!$F$2:$F$99=J2, Sheet2!$A$2:$A$99)),0)



this is an array formula so dont just hit enter

hit Ctrl-Shift-Enter



this will give you curly brackets { and } around your formula

Answer 2

that sounds like it would choose an extremely complicated macro and that i've got used spreadsheets for 22 yrs and barely use macros. I t could be greater easy to establish columns on each and each sheet for each style of cost (food, gas, and so forth), upload a sum functionality to each and each column and then have a important precis web page that only provides the great of each and every diverse cost columns (like food) and exhibits the totals all you may desire to do is make each and every sheet set up precisely an identical - the two comparable codes in an identical row or column on each and each sheet, then you definately do no longer might desire to pass attempting to locate them with a analyze functionality, you get create an basic formula including an identical cellular from each and each sheet

Answer 3

Create another column in Sheet2 that concatenates the user and date, e.g. =A2&TEXT(F2,"YYYYMMDD"). Sort Sheet2 on this column, then use it as your search field for VLOOKUP in Sheet1.