repeating column vlookup

guamlenahans

Board Regular
Joined
Oct 25, 2006
Messages
113
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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.
 

guamlenahans

Board Regular
Joined
Oct 25, 2006
Messages
113
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

Well-known Member
Joined
Nov 6, 2013
Messages
853
IF data is in range A3:J10 and reference value is in L4 formula is:
Code:
=SUMPRODUCT(--($A$3:$I$10=L4),$B$3:$J$10)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,826
Messages
5,855,875
Members
431,771
Latest member
CoryMelth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top