Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two vlookup questions
Greetings everyone,
First question, is there anyway to "optimize" this formula: =IF(J3="","",IF(J3="F",0,IF(J3="P",60,IF(J3<AU$6,1 00,IF(E3="M",IF(ISERROR(VLOOKUP(J3,'Male Tables'!$AJ$4:AT$140,AR3)),"",VLOOKUP(J3,'Male Tables'!$AJ$4:AT$140,AR3)),IF(ISERROR(VLOOKUP(J3,' Female Tables'!$AH$4:AR$140,AR3)),"",VLOOKUP(J3,'Female Tables'!$AH$4:AR$140,AR3))))))) Second question, my first column is a time assending column split every 6 seconds, my vlookup works as Microsoft has it, but I need to know how to do this... time value1 value2 value3 12:54 100 100 100 13:00 99 100 100 13:06 98 99 100 13:12 97 98 99 so if the time I'm searching for turns out to be 13:05 (not doing exact match here) based off of value 3, it will default to pulling up the 13:12 time of "99" when I actually need it to pull up the "100" in value3, is there anyway to tell the vlookup not to go to the next value and read the first value it finds? I hope I made sense of this. Very Respectfully -- Just an ordinary Joe |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two vlookup questions
For the second part
VLOOKUP has a fourth argument which can be TRUE or FALSE . Here is how the help file describes it; Syntax VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup) Where - Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned. This means that 13:05 will default to the value against 13:00 and NOT 13:12 as mentioned by you... "D.Jessup" wrote: Greetings everyone, First question, is there anyway to "optimize" this formula: =IF(J3="","",IF(J3="F",0,IF(J3="P",60,IF(J3<AU$6,1 00,IF(E3="M",IF(ISERROR(VLOOKUP(J3,'Male Tables'!$AJ$4:AT$140,AR3)),"",VLOOKUP(J3,'Male Tables'!$AJ$4:AT$140,AR3)),IF(ISERROR(VLOOKUP(J3,' Female Tables'!$AH$4:AR$140,AR3)),"",VLOOKUP(J3,'Female Tables'!$AH$4:AR$140,AR3))))))) Second question, my first column is a time assending column split every 6 seconds, my vlookup works as Microsoft has it, but I need to know how to do this... time value1 value2 value3 12:54 100 100 100 13:00 99 100 100 13:06 98 99 100 13:12 97 98 99 so if the time I'm searching for turns out to be 13:05 (not doing exact match here) based off of value 3, it will default to pulling up the 13:12 time of "99" when I actually need it to pull up the "100" in value3, is there anyway to tell the vlookup not to go to the next value and read the first value it finds? I hope I made sense of this. Very Respectfully -- Just an ordinary Joe |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two vlookup questions
For your second question, it sounds like you have hh:mm as your lookup value
and mm:ss as your table, or strings for one, and times for the other - that will pull up the last value. Try changing the format to number with three or four decimal places. If they continue to look like times, then they are strings, and if the values don't match one may be hours and the other minutes - some may even have days in them... HTH, Bernie MS Excel MVP "D.Jessup" wrote in message ... Greetings everyone, First question, is there anyway to "optimize" this formula: =IF(J3="","",IF(J3="F",0,IF(J3="P",60,IF(J3<AU$6,1 00,IF(E3="M",IF(ISERROR(VLOOKUP(J3,'Male Tables'!$AJ$4:AT$140,AR3)),"",VLOOKUP(J3,'Male Tables'!$AJ$4:AT$140,AR3)),IF(ISERROR(VLOOKUP(J3,' Female Tables'!$AH$4:AR$140,AR3)),"",VLOOKUP(J3,'Female Tables'!$AH$4:AR$140,AR3))))))) Second question, my first column is a time assending column split every 6 seconds, my vlookup works as Microsoft has it, but I need to know how to do this... time value1 value2 value3 12:54 100 100 100 13:00 99 100 100 13:06 98 99 100 13:12 97 98 99 so if the time I'm searching for turns out to be 13:05 (not doing exact match here) based off of value 3, it will default to pulling up the 13:12 time of "99" when I actually need it to pull up the "100" in value3, is there anyway to tell the vlookup not to go to the next value and read the first value it finds? I hope I made sense of this. Very Respectfully -- Just an ordinary Joe |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two vlookup questions
So I guess I will need to use INDEX and MATCH in order to find what I am
looking for. Thank you very much for taking the time to answer one of my questions. -- Just an ordinary Joe "Sheeloo" wrote: For the second part VLOOKUP has a fourth argument which can be TRUE or FALSE . Here is how the help file describes it; Syntax VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup) Where - Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned. This means that 13:05 will default to the value against 13:00 and NOT 13:12 as mentioned by you... "D.Jessup" wrote: Greetings everyone, First question, is there anyway to "optimize" this formula: =IF(J3="","",IF(J3="F",0,IF(J3="P",60,IF(J3<AU$6,1 00,IF(E3="M",IF(ISERROR(VLOOKUP(J3,'Male Tables'!$AJ$4:AT$140,AR3)),"",VLOOKUP(J3,'Male Tables'!$AJ$4:AT$140,AR3)),IF(ISERROR(VLOOKUP(J3,' Female Tables'!$AH$4:AR$140,AR3)),"",VLOOKUP(J3,'Female Tables'!$AH$4:AR$140,AR3))))))) Second question, my first column is a time assending column split every 6 seconds, my vlookup works as Microsoft has it, but I need to know how to do this... time value1 value2 value3 12:54 100 100 100 13:00 99 100 100 13:06 98 99 100 13:12 97 98 99 so if the time I'm searching for turns out to be 13:05 (not doing exact match here) based off of value 3, it will default to pulling up the 13:12 time of "99" when I actually need it to pull up the "100" in value3, is there anyway to tell the vlookup not to go to the next value and read the first value it finds? I hope I made sense of this. Very Respectfully -- Just an ordinary Joe |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two vlookup questions
I am using [h]:mm in my time field. I guess I could change this is number
format and then resort my columns for the vlookup to work correctly, unless there is another argument for vlookup to actually select the first match between my two times, instead of by default going to the next highest. I'm guessing I will have to use some form of INDEX and MATCH to accomplish what I am needing unless I of course change the column as you suggested. Thank you very much for taking the time to answer my question. I guess if I change my formula to an INDEX and MATCH then I don't have to worry about trying to optimize my vlookup forumla that I am currently using. Very Respectfully. -- Just an ordinary Joe "Bernie Deitrick" wrote: For your second question, it sounds like you have hh:mm as your lookup value and mm:ss as your table, or strings for one, and times for the other - that will pull up the last value. Try changing the format to number with three or four decimal places. If they continue to look like times, then they are strings, and if the values don't match one may be hours and the other minutes - some may even have days in them... HTH, Bernie MS Excel MVP "D.Jessup" wrote in message ... Greetings everyone, First question, is there anyway to "optimize" this formula: =IF(J3="","",IF(J3="F",0,IF(J3="P",60,IF(J3<AU$6,1 00,IF(E3="M",IF(ISERROR(VLOOKUP(J3,'Male Tables'!$AJ$4:AT$140,AR3)),"",VLOOKUP(J3,'Male Tables'!$AJ$4:AT$140,AR3)),IF(ISERROR(VLOOKUP(J3,' Female Tables'!$AH$4:AR$140,AR3)),"",VLOOKUP(J3,'Female Tables'!$AH$4:AR$140,AR3))))))) Second question, my first column is a time assending column split every 6 seconds, my vlookup works as Microsoft has it, but I need to know how to do this... time value1 value2 value3 12:54 100 100 100 13:00 99 100 100 13:06 98 99 100 13:12 97 98 99 so if the time I'm searching for turns out to be 13:05 (not doing exact match here) based off of value 3, it will default to pulling up the 13:12 time of "99" when I actually need it to pull up the "100" in value3, is there anyway to tell the vlookup not to go to the next value and read the first value it finds? I hope I made sense of this. Very Respectfully -- Just an ordinary Joe |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two vlookup questions
D,
INDEX and MATCH have the same limitations as VLOOKUP - INDEX and MATCH are most useful when the ranges are not aligned, or are out of column order. HTH, Bernie MS Excel MVP "D.Jessup" wrote in message ... I am using [h]:mm in my time field. I guess I could change this is number format and then resort my columns for the vlookup to work correctly, unless there is another argument for vlookup to actually select the first match between my two times, instead of by default going to the next highest. I'm guessing I will have to use some form of INDEX and MATCH to accomplish what I am needing unless I of course change the column as you suggested. Thank you very much for taking the time to answer my question. I guess if I change my formula to an INDEX and MATCH then I don't have to worry about trying to optimize my vlookup forumla that I am currently using. Very Respectfully. -- Just an ordinary Joe "Bernie Deitrick" wrote: For your second question, it sounds like you have hh:mm as your lookup value and mm:ss as your table, or strings for one, and times for the other - that will pull up the last value. Try changing the format to number with three or four decimal places. If they continue to look like times, then they are strings, and if the values don't match one may be hours and the other minutes - some may even have days in them... HTH, Bernie MS Excel MVP "D.Jessup" wrote in message ... Greetings everyone, First question, is there anyway to "optimize" this formula: =IF(J3="","",IF(J3="F",0,IF(J3="P",60,IF(J3<AU$6,1 00,IF(E3="M",IF(ISERROR(VLOOKUP(J3,'Male Tables'!$AJ$4:AT$140,AR3)),"",VLOOKUP(J3,'Male Tables'!$AJ$4:AT$140,AR3)),IF(ISERROR(VLOOKUP(J3,' Female Tables'!$AH$4:AR$140,AR3)),"",VLOOKUP(J3,'Female Tables'!$AH$4:AR$140,AR3))))))) Second question, my first column is a time assending column split every 6 seconds, my vlookup works as Microsoft has it, but I need to know how to do this... time value1 value2 value3 12:54 100 100 100 13:00 99 100 100 13:06 98 99 100 13:12 97 98 99 so if the time I'm searching for turns out to be 13:05 (not doing exact match here) based off of value 3, it will default to pulling up the 13:12 time of "99" when I actually need it to pull up the "100" in value3, is there anyway to tell the vlookup not to go to the next value and read the first value it finds? I hope I made sense of this. Very Respectfully -- Just an ordinary Joe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP questions | Excel Worksheet Functions | |||
Vlookup and few more questions :) | Excel Worksheet Functions | |||
VLOOKUP - 2 Questions | Excel Worksheet Functions | |||
VLOOKUP Questions | Excel Worksheet Functions | |||
vlookup and calculation questions | Excel Worksheet Functions |