First:Your EMP ID field is mixed with "text" and "numbers".Probably because 
this list was imported from another source.(generated a report as a text file 
and opened it in Excel?)
then, if you click in one of the cells, or remove spaces, or interact with the 
cell in several other ways, Excel reinterprets the cell contents as "numeric".
(that's why some of your cells have a green triangle in the top-left.It 
indicates that the cell format is different than the previous)
this is significant because when you type the EMP ID in Sheet9, Excel will 
automatically treat it as a NUMBER.then, when you look up and compare the 
values in "Data" with the value in Sheet9, a "text" 123 does NOT equal a NUMBER 
123..
So you need to make them the same format.
What *I* did was select column "B" and replace "2" with 2.Excel then converted 
the entire column to a numeric.
NEXT:
the =VLOOKUP() function has the format:VLOOKUP(lookup_value, table_array, 
col_index_num, [range_lookup])
In this, the [range_lookup] flag indicates if you want an "EXACT MATCH" or 
"APPROXIMATE MATCH".
To use this properly, your Data needs to be sorted by EMP ID and then by 
"Period To".
Then, in Sheet9 use the formula:=VLOOKUP(E2,Data!B:E,4,TRUE)
Interestingly enough, if you use Range_Lookup = FALSE, Vlookup will return the 
FIRST line that exactly matches the EMP_ID.If you set it to TRUE, it'll return 
the LAST record.
So, if you sort the data by EMP_ID and Newest-to-oldest of the "Period To", you 
would use Range_Lookup = FALSE.if you sorted by EMP_ID and oldest-to-newest of 
"Period To", you would use Range_Lookup = TRUE.
I also added an indicator to the Data sheet to see which PRF is selected (for 
testing purposes)
Hope this helps. Paul-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
 
      From: SridharBL <sridhar....@gmail.com>
 To: excel-macros@googlegroups.com 
 Sent: Tuesday, July 21, 2015 7:39 AM
 Subject: $$Excel-Macros$$ Formula to Display Latest PO number Using date and 
emp ID
   
Dear Friends,
I need help in creating the formula in attached file.
In attached sheet. I have Sheet9 and  Data Sheets.
In Sheep 9, If I type the Emp ID in the cell, its should look for data in Data 
sheet for the Emp ID and Latest Date for in column ""Period To" and get the New 
PO number that will be displayed in next cell.
Attache file is self explanatory.
Request you help.
ThanksSridhar BL-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel
 
FORUM RULES
 
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
 
NOTE : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.


   

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.

Attachment: Finding PO Number.xlsx
Description: MS-Excel 2007 spreadsheet

Reply via email to