NewBee and vlookup with 2 criteria

[splreece]

Morning all,

I apologise for the simple nature of the query but I am a formulae newbee.

So any help is greatly appreciated.


I have the following sample dataset:

A1 A2 A3 A4 A5 A6 A7
Office Date Day Crates on hand Crates received Crates Sent Balance

Durham 26/05/2015 Tues 62 56 28 90
Durham 27/05/2015 Wed 90 60 90 27
Belfast 31/03/2014 Mon 53 14 13 40
Belfast 04/04/2014 Fri 28 12 16 24
Liverpool 25/05/2015 Mon 37 0 10 20

etc......


This is a sample from an order sheet we keep for several offices which is updated daily with their needs.

What I want to do is have an automated order sheet on a separate tab that pulls the relevant detail from the cells.... however I seem to be stumbling as the cell response is "N/A".

For example...=VLOOKUP(B5&B3,Sheet1!A5:P1048576,5,FALSE)

I have 2 user entry options in B5 & B3 where users can dictate date and location (so effectively you can pull order results for anyday/office.


However it doesn't seem to work...

I've got a sample dataset from a fantastic site that explains it well and I can manipulate that but when I transfer it to my data, it wont work (all my data is manually keyed so no formulae or calculations exist behind the cells).

(sample from downloaded file)
Name: Jeremy Hill
Product: Sweets
Qty. 2

Customer&Product Customer Product Qty.
Dan Brown 42271 Dan Brown 24/09/2015 14
Dan Brown Biscuits Dan Brown Biscuits 7
Dan Brown Sweets Dan Brown Sweets 19
Dan Brown 41729 Dan Brown 31/03/2014 15
Jeremy Hill 42271 Jeremy Hill 24/09/2015 8
Jeremy Hill Sweets Jeremy Hill Sweets 2
Robert Acey Biscuits Robert Acey Biscuits 5
Robert Acey Lemons Robert Acey Lemons 11
Robert Acey Pies Robert Acey Pies 17
Robert Acey Sweets Robert Acey Sweets 16
Tom Boone Apples Tom Boone Apples 20
Tom Boone Sweets Tom Boone Sweets 4

 

[btothed03]

I was able to successfully carry out this function. Make sure you pay close attention to the formatting. More than likely, you're experiencing an issue with that. The formatting and string combination needs be EXACT. You can try using "TRUE" instead of "FALSE" within your VLOOKUP formula; however, there may be undesired issues with that as well.

 

[CharlesChuckieCharles]

VLOOKUP(B5&B3,Sheet1!A5:P1048576,5,FALSE)

VLOOKUP uses the first parameter (B5&B3) to look up the values in the left most column of the data set (Sheet1:A5..) and returns the the specified column (5) on the row of the first match it finds

Therefore Column A on Sheet1 should contain data like 26/05/2015DURHAM but in fact it will be more like 42150DURHAM ( based on B5&B3)

using FALSE means sheet1 doesn't have to be sorted ( but slower over large amounts of data)