Two vlookup questions

[D.Jessup]

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,100,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

 

[Sheeloo]

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,range_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...

 

[Bernie Deitrick]

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]

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.

 

[D.Jessup]

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.

 

[Bernie Deitrick]

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

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

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