Searching multiple data to show desired result

[lawandgrace]

Here are the results of my query:

Sheet2:

A B C D
1 CustomerName Date Location No.
2 Customer1 7/6/2009 1A 1 - 8
3 Customer1 7/13/2009 1A 1
4 Customer1 7/15/2009 1A NA
5 Customer1 7/17/2009 1A 1-8

These results are on Sheet2. On Sheet1, I have something that looks like
this (the top row of A thru E and the side row of 1 thru 6 are supposed to
represent the Excel columns/rows):

Sheet1:

A B C D
1 1 2 3
2 A 8
3 B
4 C
5 D

I want the formula in Sheet1, Column B, Row2, to look at the query on Sheet2
for the matching Location (in this case, 1A), the most recent date
(7/17/2009) and show the number in Column D to the furthest right (in this
case it is 1-8 and I only want it to show the 8).

In the original query there are multiple locations (i.e., 1A, 2A, 3A, etc.,
and 1B, 2B, 3B, etc., and so forth).

I hope this is understandable. Thanks for your help.

 

[Max]

In Sheet1,
Place in B2, array-enter, ie confirm the formula via pressing
CTRL+SHIFT+ENTER
=INDEX(Sheet2!$D$2:$D$100,MATCH(MAX(IF(Sheet2!$C$2:$C$100=B$1&$A2,Sheet2!$B$2:$B$100)),IF(Sheet2!$C$2:$C$100=B$1&$A2,Sheet2!$B$2:$B$100),0))
Copy B2 across/fill down to return the results "as-is" from Sheet2's col D

And if you want to return only the "rightmost" number (ie the number after
the hyphen), then indicatively, using MID, it'll look like this:
=MID(xx,SEARCH("-",xx)+1,99)+0
where xx is the above array formula
and the "+0" is to coerce the text number returned to a real number

Hence, you could try instead in B2, array-entered as before
=MID(INDEX(Sheet2!$D$2:$D$100,MATCH(MAX(IF(Sheet2!$C$2:$C$100=B$1&$A2,Sheet2!$B$2:$B$100)),IF(Sheet2!$C$2:$C$100=B$1&$A2,Sheet2!$B$2:$B$100),0)),SEARCH("-",INDEX(Sheet2!$D$2:$D$100,MATCH(MAX(IF(Sheet2!$C$2:$C$100=B$1&$A2,Sheet2!$B$2:$B$100)),IF(Sheet2!$C$2:$C$100=B$1&$A2,Sheet2!$B$2:$B$100),0)))+1,99)+0
Copy B2 across/fill down to populate

Success? Celebrate it, hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[lawandgrace]

Sorry, I've tried several times and I keep getting the #N/A error code. Also,
I do not have to search for the dash as not all the returned data will
necessarily have a dash (it might return a single number), so for that
instance I think I just need something like =RIGHT(cell,1). But I could be
wrong

 

[Max]

Precisely the reason why I responded with the core array expression to
retrieve it "as-is" from Sheet2's col D, separate from ensuing action to
strip out what-have-you.

There could be various reasons why the #N/A:
a. Did you correctly array-enter it, before propagating across/down? Visual
check by looking in the formula bar, there should be curly braces: {..}
wrapped by Excel, post confirmation. If there's none, then it isn't
array-entered, and you have to confirm it again (click inside the formula
bar, do the CSE), and re-check for the curlies.

b. If (a) is ok, then there's data inconsistency preventing what should
otherwise have matched correctly, eg extra white spaces. You could use TRIM
for increased robustness in the matching of the concat lookups vs the array
in Sheet2's col C, eg:
=INDEX(Sheet2!$D$2:$D$100,MATCH(MAX(IF(TRIM(Sheet2!$C$2:$C$100)=TRIM(B$1&$A2),Sheet2!$B$2:$B$100)),IF(TRIM(Sheet2!$C$2:$C$100)=TRIM(B$1&$A2),Sheet2!$B$2:$B$100),0))

Get the above returning correctly first before you proceed to tackle the 2nd
part. You could start a new thread for the 2nd part, elaborating on the full
range of possible values in Sheet2's col D, and what you want stripped out.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik