Hi guys,
This is my first post and I'm looking for some help with a vlookup function from 2 columns.
I have sheet1 with the columns;
sales date
sales person
sales number
In sheet2 I want a function that looks up the "sales number" based on "sales date" and "sales person".
Is it possible to have 2 vlookup criteria in one function?
Many many thanks!
Adrian
Yes it is. But i am not able to understand how your data looks like... Pls upload a small sample workbook...is it possible to have 2 vlookup criteria in one function?
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Hi Adriano85
Assuming following:
Dates in column A
Sales person in Column B
sales number Column C
Try the following:
=LOOKUP(2,1/((A:A=DATE(2013,4,22))*(B:B="Fred")),C:C)
Change the date & sales person to suit
Regards Kevin
Merged Cells (They are the work of the devil!!!)
Hi guys,
Please find attached an example of the sheet. I'm looking for a formula to find the number of bookings based on date and recruiter.
Many thanks for your help!
Hi Kevin,
What does the 2,1 do? Would like to understand the functions so it will be easier to use them in the future.
Unfortunately it's not working yet.
Adrian
Use same date format in H2 as column A. Then in J2 use this formula that gives 1 as result.
=SUMPRODUCT((A2:A100=H2)*(B2:B100=I2)*(C2:C100))
Very strange... I used exactly the same formula and the result is 0 while the booking in the table is 1.
Do you have any idea while its not returning the correct number?
Originally Posted by Fotis1991
........................................
H2 does not contain an actual date value-it has text that looks like a date. try entering it again
Josie
if at first you don't succeed try doing it the way your wife told you to
Hey guys,
I changed both formats into "date" but still 0 as result. What am I doing wrong?
See this .......................
replace the & in your formula with *
Thanks for your great help. Much appreciated!
I am wondering now, what if there are bookings for 2 or more countries? How can we let this function do a horizontal lookup in the column heading?
Is this even possible?
..............................
=SUMPRODUCT(($A$3:$A$101=H3)*($B$3:$B$101=I3)*INDEX($C$3:$D$101,,MATCH(J3,$C$2:$D$2,0)))
for instance
Do you have any idea why I get a reference error? (see attachment)
=sumproduct(($a$3:$a$101=h3)*($b$3:$b$101=i3)*($c$2:$d$2=j3)*($c$3:$d$101))
Awesome!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks