VLookup Substitute

Is there another formula that works like vlookup but isn't concerned about the source data being in ascending order? I know of one that works when you can look up based on 2 conditions but can't seem to make it work when I only have 1 condition to meet.

My 2 condition formula is a CSE and is: =index(array to lookup(match(1,array of first condition=condition)*(array of 2nd condition=2nd condition))

Can I do something like the above if I only have one condition to meet?

VLOOKUP does not have to have the data in ascending order if you use the parameter FALSE as the last condition.

Ed

Well, that was easy! Thank you!

and this answered my question.

Why would you ever use the TRUE argument then ?

You may want to use TRUE in your argument if the data is already in order, or if you are looking for the next closest value within the array. So if the exact isn't found, you may be OK with the next closest variable.

Range_lookup A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:

If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

The values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value. You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending. For more information, see Default sort orders.

If FALSE, VLOOKUP will only find an exact match. In this case, the values in the first column of table_array do not need to be sorted. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.

Hope that helps.