repeating column vlookup

[guamlenahans]

Hello,

I have a sheet title "20K Vol" that measure how much liquid is in a container in 1/8 inch increments. On the sheet, there are two columns that repeat over and over. Column A is "inches" and Column B is "gallons. This repeats in C/D, E/F and so on.

My question is, since this in not in one long list in two columns, how do I do a lookup? My desired end state, is on a separate sheet inside the same workbook, when I type in a inch value in column A I will get the result from the 20K Vol sheet in column B.

Thanks

Rob

 

[Aladin Akyurek]

Hard to visualize the lay-out. Try to post a small sample (5 rows) from A:D and specify what the look up value is and where that value is located.

 

[kvsrinivasamurthy]

If the container is cylindrical the diameter of Cylinder ie required.

 

[guamlenahans]

Here is an example. If I have 13 3/4 in Column D on sheer 1, want to look in the info below (which will be on another sheet) for the value and have it put into column E.


Height (in) Gallons Height (in) Gallons Height (in) Gallons Height (in) Gallons Height (in) Gallons
1/8 8.81 12 1/8 854.84 24 1/8 1700.87 36 1/8 2546.89 48 1/8 3392.92
1/4 17.63 12 1/4 863.65 24 1/4 1709.68 36 1/4 2555.71 48 1/4 3401.73
3/8 26.44 12 3/8 872.47 24 3/8 1718.49 36 3/8 2564.52 48 3/8 3410.55
1/2 35.25 12 1/2 881.28 24 1/2 1727.30 36 1/2 2573.33 48 1/2 3419.36
5/8 44.06 12 5/8 890.09 24 5/8 1736.12 36 5/8 2582.14 48 5/8 3428.17
3/4 52.88 12 3/4 898.90 24 3/4 1744.93 36 3/4 2590.96 48 3/4 3436.98
7/8 61.69 12 7/8 907.72 24 7/8 1753.74 36 7/8 2599.77 48 7/8 3445.80
1 70.50 13 916.53 25 1762.56 37 2608.58 49 3454.61

 

[kvsrinivasamurthy]

IF data is in range A3:J10 and reference value is in L4 formula is:

=SUMPRODUCT(--($A$3:$I$10=L4),$B$3:$J$10)