Need help looking up value in table based on 'Name' & 'Date'

[Chookz]

Hey everyone,

Here is my example workbook:

Excel 2007

	A	B	C	D	E
1	Date	Name	Location	Start Time	Weight
2	11/04/2013	Smith, John	Australia	11:00 AM	75.3
3	11/04/2013	Doe, Jane	Australia	11:00 AM	90.7
4	12/04/2013	Bob, Billy	New Zealand	11:00 AM	94.6

<tbody>
</tbody>

Sheet5

I would like a formula to enter in cell B3 of the following Sheet that will tell me the location for Billy Bob on the 12/4/13 from the table in Sheet 5.

Excel 2007

	A	B
1	Name:	Bob, Billy
2	Date:	12/04/2013
3	Location:

<tbody>
</tbody>

Sheet 6
Im assuming it's some kind of Vlookup but I don't have the knowledge of what the exact formula would be.​

Thanks in advance

(P.S. this is the first time ive used HTMLMaker so apologies for things being a little messy)

Charles

 

[s hal]

Actually, you'll be using an index() and match() combination CSE function (much more versatile than vlookup or hlookup)

=INDEX('Sheet5'!$C$2:$C$4,MATCH(B2&B1,'Sheet5'!$A$2:$A$4&'Sheet5'!$B$2:$B$4,0))
Entered with CTRL-SHIFT-Enter! (do NOT forget this part or you will get a #N/A error!)

Hope this helps!

 

[Chookz]

Legendary!
Thanks for the super quick reply s hal, that worked a treat.
Just out of curiosity, what does CTRL-SHIFT-Enter do and when is it used?

 

[Aladin Akyurek]

Sheet 6, B3, control+shift+enter, not just enter:

=INDEX(Sheet5!$C$2:$C$4,MATCH(B1,IF(Sheet5!$A$2:$A$4=B2,Sheet5!$B$2:$B$4),0))

 

[s hal]

Legendary!
Thanks for the super quick reply s hal, that worked a treat.
Just out of curiosity, what does CTRL-SHIFT-Enter do and when is it used?

it signifies to excel that you are entering an array formula (arrays generally meaning you are accessing and doing things across multiple cells at once) rather than a standard formula. It makes { } brackets around your formula as well I would definitely suggest reading up on "CSE" functions as they really add a ton of functionality to your excel abilities as a whole!

 

[Chookz]

^ Great, thanks again. Much appreciated