| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup for latest data
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(VLOOK UP(emp code,'sheet2'!D:R,15,0))=true,""),(IF(ISERROR(VLOO KUP(emp code,'sheet2'!D:Q,14,0))=true,""),(vlookup(emp code,'sheet2'!C:O,13,0))=true,""))))) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup for latest data
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? -------------------- (MS-Exl-Learner) -------------------- "Tahira" wrote: 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(VLOOK UP(emp code,'sheet2'!D:R,15,0))=true,""),(IF(ISERROR(VLOO KUP(emp code,'sheet2'!D:Q,14,0))=true,""),(vlookup(emp code,'sheet2'!C:O,13,0))=true,""))))) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup for latest data
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. -- Biff Microsoft Excel MVP "Tahira" wrote in message ... 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(VLOOK UP(emp code,'sheet2'!D:R,15,0))=true,""),(IF(ISERROR(VLOO KUP(emp code,'sheet2'!D:Q,14,0))=true,""),(vlookup(emp code,'sheet2'!C:O,13,0))=true,""))))) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup for latest data
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" wrote: 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? -------------------- (MS-Exl-Learner) -------------------- "Tahira" wrote: 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(VLOOK UP(emp code,'sheet2'!D:R,15,0))=true,""),(IF(ISERROR(VLOO KUP(emp code,'sheet2'!D:Q,14,0))=true,""),(vlookup(emp code,'sheet2'!C:O,13,0))=true,""))))) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup for latest data
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 its 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,S heet2!D:S,16,FALSE),IF(VLOOKUP(B2,Sheet2!D:S,15,FA LSE)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,VLOOK UP(B2,Sheet2!D:S,13,FALSE),IF(VLOOKUP(B2,Sheet2!D: S,12,FALSE)0,VLOOKUP(B2,Sheet2!D:S,12,FALSE),IF(V LOOKUP(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,S heet2!D:R,15,FALSE),IF(VLOOKUP(B2,Sheet2!D:R,14,FA LSE)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,VLOOK UP(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. -------------------- (MS-Exl-Learner) -------------------- "Tahira" wrote: 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" wrote: 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? -------------------- (MS-Exl-Learner) -------------------- "Tahira" wrote: 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(VLOOK UP(emp code,'sheet2'!D:R,15,0))=true,""),(IF(ISERROR(VLOO KUP(emp code,'sheet2'!D:Q,14,0))=true,""),(vlookup(emp code,'sheet2'!C:O,13,0))=true,""))))) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup for latest data
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 "MS-Exl-Learner" wrote: 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 its 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,S heet2!D:S,16,FALSE),IF(VLOOKUP(B2,Sheet2!D:S,15,FA LSE)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,VLOOK UP(B2,Sheet2!D:S,13,FALSE),IF(VLOOKUP(B2,Sheet2!D: S,12,FALSE)0,VLOOKUP(B2,Sheet2!D:S,12,FALSE),IF(V LOOKUP(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,S heet2!D:R,15,FALSE),IF(VLOOKUP(B2,Sheet2!D:R,14,FA LSE)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,VLOOK UP(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. -------------------- (MS-Exl-Learner) -------------------- "Tahira" wrote: 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" wrote: 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? -------------------- (MS-Exl-Learner) -------------------- "Tahira" wrote: 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(VLOOK UP(emp code,'sheet2'!D:R,15,0))=true,""),(IF(ISERROR(VLOO KUP(emp code,'sheet2'!D:Q,14,0))=true,""),(vlookup(emp code,'sheet2'!C:O,13,0))=true,""))))) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup for latest data
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",A1:D5,MATCH("Wipro",A1:D1,0),0) You can change the text to cell references If this post helps click Yes --------------- Jacob Skaria "Tahira" wrote: 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 "MS-Exl-Learner" wrote: 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 its 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,S heet2!D:S,16,FALSE),IF(VLOOKUP(B2,Sheet2!D:S,15,FA LSE)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,VLOOK UP(B2,Sheet2!D:S,13,FALSE),IF(VLOOKUP(B2,Sheet2!D: S,12,FALSE)0,VLOOKUP(B2,Sheet2!D:S,12,FALSE),IF(V LOOKUP(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,S heet2!D:R,15,FALSE),IF(VLOOKUP(B2,Sheet2!D:R,14,FA LSE)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,VLOOK UP(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. -------------------- (MS-Exl-Learner) -------------------- "Tahira" wrote: 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" wrote: 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? -------------------- (MS-Exl-Learner) -------------------- "Tahira" wrote: 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(VLOOK UP(emp code,'sheet2'!D:R,15,0))=true,""),(IF(ISERROR(VLOO KUP(emp code,'sheet2'!D:Q,14,0))=true,""),(vlookup(emp code,'sheet2'!C:O,13,0))=true,""))))) |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup for latest data
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 its 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 dont 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€¦ -------------------- (MS-Exl-Learner) -------------------- "Tahira" wrote: 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 "MS-Exl-Learner" wrote: 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 its 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,S heet2!D:S,16,FALSE),IF(VLOOKUP(B2,Sheet2!D:S,15,FA LSE)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,VLOOK UP(B2,Sheet2!D:S,13,FALSE),IF(VLOOKUP(B2,Sheet2!D: S,12,FALSE)0,VLOOKUP(B2,Sheet2!D:S,12,FALSE),IF(V LOOKUP(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,S heet2!D:R,15,FALSE),IF(VLOOKUP(B2,Sheet2!D:R,14,FA LSE)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,VLOOK UP(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. -------------------- (MS-Exl-Learner) -------------------- "Tahira" wrote: 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" wrote: 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? -------------------- (MS-Exl-Learner) -------------------- "Tahira" wrote: 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(VLOOK UP(emp code,'sheet2'!D:R,15,0))=true,""),(IF(ISERROR(VLOO KUP(emp code,'sheet2'!D:Q,14,0))=true,""),(vlookup(emp code,'sheet2'!C:O,13,0))=true,""))))) |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup for latest data
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:D,0)&":R"&MATCH(B2,Sheet2!D:D,0)))),"No Dates found",LOOKUP(10^10,INDIRECT("Sheet2!O"&MATCH(B2,S heet2!D:D,0)&":R"&MATCH(B2,Sheet2!D:D,0)))) If this post helps click Yes --------------- Jacob Skaria "MS-Exl-Learner" wrote: 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 its 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 dont 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€¦ -------------------- (MS-Exl-Learner) -------------------- "Tahira" wrote: 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 "MS-Exl-Learner" wrote: 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 its 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,S heet2!D:S,16,FALSE),IF(VLOOKUP(B2,Sheet2!D:S,15,FA LSE)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,VLOOK UP(B2,Sheet2!D:S,13,FALSE),IF(VLOOKUP(B2,Sheet2!D: S,12,FALSE)0,VLOOKUP(B2,Sheet2!D:S,12,FALSE),IF(V LOOKUP(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,S heet2!D:R,15,FALSE),IF(VLOOKUP(B2,Sheet2!D:R,14,FA LSE)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,VLOOK UP(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. -------------------- (MS-Exl-Learner) -------------------- "Tahira" wrote: 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" wrote: 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? -------------------- (MS-Exl-Learner) -------------------- "Tahira" wrote: 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(VLOOK UP(emp code,'sheet2'!D:R,15,0))=true,""),(IF(ISERROR(VLOO KUP(emp code,'sheet2'!D:Q,14,0))=true,""),(vlookup(emp code,'sheet2'!C:O,13,0))=true,""))))) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Latest LOOKup | Excel Worksheet Functions | |||
| always recheck data connection library in MOSS for latest data sou | Excel Discussion (Misc queries) | |||
| Lookup / return latest date in range AND value of 1 column to the | Excel Worksheet Functions | |||
| lookup latest entered match | Excel Worksheet Functions | |||
| Lookup the latest date in a range so it appears as my result | Excel Discussion (Misc queries) | |||
Linear Mode
