lookup for latest data

[Tahira]

I have two sheets, first contains employee details like emp num and names
designation etc.... and second sheet contains the starting date of the
employees and 4 other columns withtheir date of extention... extn1,
extn2,extn3,extn4

In the first sheet I need the latest date of extention which should be taken
from second sheet. first it should considerlatest date from Exten4, if there
is no date in that column it should look out for next date in extn3 and bring
the date or subsequently repeat the same till it gets date from extn2 ot extn1

I tried the below formulae which did not word

=IF(ISERROR(IF(ISERROR(VLOOKUP(emp
code,'sheet2'!D:S,16,0))=true,"",(IF(ISERROR(VLOOKUP(emp
code,'sheet2'!D:R,15,0))=true,""),(IF(ISERROR(VLOOKUP(emp
code,'sheet2'!D:Q,14,0))=true,""),(vlookup(emp
code,'sheet2'!C:O,13,0))=true,"")))))

 

[MS-Exl-Learner]

Confirm whether emp num or names which is available in Sheet1 is present in
Sheet2 also, if it is available in sheet2 then say in which column it is
their in Sheet2?

 

[T. Valko]

Will there be at least one date in those cells?

sheet2'!D:S,16,0))
sheet2'!D:R,15,0))
sheet2'!D:Q,14,0))
sheet2'!C:O,13,0))

What's in column P? This would be relatively easy if the range to search is
a contiguous range.

 

[Tahira]

yes, the emp code present in both the sheets, in sheet2 its in 'column d' and
exten dates are in 'column O' to 'colum S'

 

[MS-Exl-Learner]

Hi,

In your post first you have mentioned that you need to check extn1, extn2,
extn3 & extn4. but in the second post you have mentioned that the extension
dates are from Colum O to Column S. Column O to column S it’s coming around
5 Columns that is 5 Extensions.

If you require the result from Column O to column S then paste this formula

=IF(VLOOKUP(B2,Sheet2!D:S,16,FALSE)>0,VLOOKUP(B2,Sheet2!D:S,16,FALSE),IF(VLOOKUP(B2,Sheet2!D:S,15,FALSE)>0,VLOOKUP(B2,Sheet2!D:S,15,FALSE),IF(VLOOKUP(B2,Sheet2!D:S,14,FALSE)>0,VLOOKUP(B2,Sheet2!D:S,14,FALSE),IF(VLOOKUP(B2,Sheet2!D:S,13,FALSE)>0,VLOOKUP(B2,Sheet2!D:S,13,FALSE),IF(VLOOKUP(B2,Sheet2!D:S,12,FALSE)>0,VLOOKUP(B2,Sheet2!D:S,12,FALSE),IF(VLOOKUP(B2,Sheet2!D:S,11,FALSE)>0,VLOOKUP(B2,Sheet2!D:S,12,FALSE),"NO VALUE FOUND IN ALL THE CELLS"))))))


If you require the result from COLUMN O TO COLUMN R then paste this formula

=IF(VLOOKUP(B2,Sheet2!D:R,15,FALSE)>0,VLOOKUP(B2,Sheet2!D:R,15,FALSE),IF(VLOOKUP(B2,Sheet2!D:R,14,FALSE)>0,VLOOKUP(B2,Sheet2!D:R,14,FALSE),IF(VLOOKUP(B2,Sheet2!D:R,13,FALSE)>0,VLOOKUP(B2,Sheet2!D:R,13,FALSE),IF(VLOOKUP(B2,Sheet2!D:R,12,FALSE)>0,VLOOKUP(B2,Sheet2!D:R,12,FALSE),IF(VLOOKUP(B2,Sheet2!D:R,11,FALSE)>0,VLOOKUP(B2,Sheet2!D:R,12,FALSE),"NO VALUE FOUND IN ALL THE CELLS")))))

Hope this is what you have asked for!

If this post helps, click yes.

 

[Tahira]

hey this is really great help, its working, thanks a bunch,

I have one more query if you can help me in that as well..

In the same file I have another sheet3 where there is a list of prices as
below

CTS Wipro HCL
SE 30 28 29
SSE 35 30 31
PM 40 35 38
TL 38 33 35

first sheet I need to arrive at rates for each employee based on his
designation (which is on column F) & vendor name (column E).
for e.g., if the designation is TL and if he is from wipro I need to arrive
at the rate 33

 

[Jacob Skaria]

Tahira

Try the below

Col A Col B Col C Col D
CTS Wipro HCL
SE 30 28 29
SSE 35 30 31
PM 40 35 38
TL 38 33 35

=VLOOKUP("TL",A15,MATCH("Wipro",A11,0),0)

You can change the text to cell references

If this post helps click Yes

 

[MS-Exl-Learner]

Thanks for feeding back and your next query was already resolved by Mr. Jacob
who is Expert in Excel.

Generally I post query for my doubts, Mr. Jacob, Mr. Max, Mr. Mike, Mr.
Pete_UK and more Excel experts give solutions for that. I am reading each
and every post of these experts and their answers are Mindblowing and at the
same time it’s very happy that I can also able to help at least for one query…

But I am sure that the formula I have suggested can be reduced by the above
experts. At the same time the formula is not perfect, because I have not
used ISNA, due to this if the value is not available in that range it will
result NA. So the ISNA function should be added on it, I don’t know how it
can be done… I hope that the experts will see this post and give solution for
this also…

Once again thanks for your feedback…

 

[Jacob Skaria]

The formula can be reduced to the below which will lookup the last entered
date in Sheet2 O:R. If this needs to be O:S change as required...

=IF(ISNA(LOOKUP(10^10,INDIRECT("Sheet2!O"&MATCH(B2,Sheet2!D,0)&":R"&MATCH(B2,Sheet2!D,0)))),"No
Dates
found",LOOKUP(10^10,INDIRECT("Sheet2!O"&MATCH(B2,Sheet2!D,0)&":R"&MATCH(B2,Sheet2!D,0))))

If this post helps click Yes