vlookup value with condition of another cell being closest to specific value in a row

[fudge123]

Hi all, im new here so be gentle.

So. I'm familiar with vlookup functions but i have a situation where i need to look beyond matching a specific cell with one simple criteria.

The additional condition is to return value in column C where the matched cell is found in column A (where there are multiple instances of that matched cell is in the data) but of those matched to return value from column C, column B must be closest to the value 10 of all occurrences (higher or lower is fine, just the closest).

The value i want returned from the list in F is listed beside it in column G.

I figure the method will use INDEX and MATCH but I'm not too familiar with these functions :S

Thanks!

[Ron Coderre]

Using your posted workbook, this regular formula, copied down, begins the list of
...Col_C values
...that correspond to the Col_B Stock value closest to 10
...where the Col_A SKU value matches the Col_F SKU

Please Login or Register  to view this content.

Note: In the case of Col_B ties, the first encountered is used.

Is that something you can work with?

[fudge123]

Ron, that works amazingly well

Thanks a million!