I need some help with a challenging lookup formula. It might (not sure though) require to combine VLookup and HLookup.
See data example below:
**************************
****
LookUp Source:
Label 10 20 30
Range_A 75 85 90
Range_B X1 X2 X3
******************************
Actual Data
Col A Col B Looked-Up Value
Row 1 15 Range_A "75" (why this value... see explanation below)
Row 2 29 Range_B "X2" (why this value... see explanation below)
Row 3 30 Range_A "90" (why this value... see explanation below)
******************************
Explanation:
Why "75" for Row 1:
o Value of 15 in A1 (Actual Data) is less than 20 Label (Lookup Source).
o Therefore, values under "10" (either 75 or X1) are considered.
o Given "Range_A" in B1 (Actual Data), we selected 75 (Lookup Source).
Why "X2" for Row 2:
o Value of 29 in A2 (Actual Data) is less than 30 Label (Lookup Source).
o Therefore, values under "20" (either 85 or X2) are considered.
o Given "Range_B" in B2 (Actual Data), we selected X2 (Lookup Source).
Why "90" for Row 3:
o Value of 30 in A3 (Actual Data) equals 30 Label (Lookup Source).
o Therefore, values under "30" (either 90 or X3) are considered.
o Given "Range_A" in B3 (Actual Data), we selected 90 (Lookup Source).
Hope this makes sense... see attached XLS for data.
Thanks,
EEH
Lookup-and-Actual-Data.xlsx