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.
Finding PO Number.xlsx
Description: MS-Excel 2007 spreadsheet
