Matching 2 cells for vlookup..

  • Thread starter Thread starter vinu
  • Start date Start date
V

vinu

Hi,

I’m trying to insert a user friendly sheet where they can enter there
“Area” “Place” and which “shift time” they will be coming to find what
time the vehicle will come to there Place to pick them.

All the details are there in the next sheet as a table with heading
like, Area-Place7.30-8.30-…-19.30-(shift times)

For area, Place & Shift time I used data validation so that they can
select them from drop down but I don’t know how to pull the pickup
time that will match to 3 other cells..

Should I use the Hlookup to get the data that will be under the
particular shift time, if that is the case thaen how can I match the
shift time with Area & Place..

I may sound confused coz I’m really confused…!!

Pls help me on this…….

Regards,
Vinu
 
Hi Vinu,

Use SUMPRODUCT to find a value that matches several criteria.

Usage is like this;
=SUMPRODUCT((criteriaRange1=value1)*(criteriaRange2=value2)*(criteriaRange3=value3)*valueRange)

So, if A1:A100 contains Area data like Perth, Sydney, Hobart, ...
and B1:B100 contains Place data like Central, South, East, ...
and C1:C100 contains shift times like 7:30, 8:00, 8:30, ...

Then,
=SUMPRODUCT(($A$1:$A$100="Hobart")*($B$1:$B$100="South")*$C$1:$C$100)
will find the shift time corresponding to Hobart, South

Ed Ferrero
www.edferrero.com
 
Hi Ed,

Thanks for the help. Just introduced myself to your website also. ïŠ

I’m not clear about one thing that the Area & Place will be like row
header and the shift time will be of Row header..
As I searched the pervious posts, I managed to come up with this
formula,

=INDEX('Sheet2'!E3:S184,MATCH(D19,'Sheet2'!C:C),MATCH(E19,'Sheet2'!
E3:S3))

Not sure but as if now its working fine.

Now there is some other thing that I could not find any solution by my
own,
But I want to refer the Area and want to have drop down that only
shows the places based on the area in the first cell.

The sorce data is in Sheet2 and it’s like Areas in “B†and Places in
“Câ€
E.g.
Perth East
Perth South
Sydney North
Sydney Central
Hobart West

Can you help me on this one also

Regards,
Vinu
 
Hi Ed,

You website looks yammi to me :)

Your giving more solutions that i didn't posted here...

I will be saing thanks for mmm... next two weeks.(hope I will finish
digging by then)

Thanks,
Vinu
 
Back
Top