Vlookup with variable table array?

Lifesaver

New Member
Joined
Nov 9, 2004
Messages
8
I am attempting to create a lookup function that searches for a value in a table array that varies according to what is found in another cell.

See in the simplified example below, lookup_value is C17 and, depending of the code typed at C18, the table array would start from the cell that contains this value (F3:H15 in this example) and it would return the equivalent amount of hours in the last column (1185.71) as the result. Obviously, my list would remain sorted to allow the 4th argument (range_lookup) to be "True".
Hourly Employees Salary Rates Chart (for MBONDU).xls
ABCDEFGH
1
2FTABFTBBFTCBPTABPTBBPTCBHours
310.509.5011.5010.509.5011.500
410.509.5011.5011.5510.6512.90385.71
511.5510.6512.9011.5510.6512.90514.29
611.5510.6512.9012.4711.1713.701185.71
712.4711.1713.7012.4711.1713.701314.29
812.4711.1713.7013.3111.9114.601985.71
913.3111.9114.6013.3111.9114.602114.29
1013.3111.9114.6014.1112.3115.502785.71
1114.1112.3115.5014.1112.3115.502914.29
1214.1112.3115.5015.0013.3816.403585.71
1315.0013.3816.4015.0013.3816.403714.29
1415.0013.3816.4016.0014.2517.404385.71
1516.0014.2517.4016.0014.2517.404514.29
16
17Rate:11.17
18Code:PTBB
19Hours:?
Sheet2


Any help that you could provide would be greatly appreciated. Thank you!
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,508
Office Version
  1. 365
Platform
  1. Windows
Lifesaver

Like this?

D18: =MATCH(C18,B2:G2,0)
Tells us what column in the table the C18 value is.
C19: =VLOOKUP(C17,OFFSET(B3:B15,,D18-1,,8-D18),8-D18,0)
Mr Excel.xls
ABCDEFGHI
1
2FTABFTBBFTCBPTABPTBBPTCBHours
310.59.511.510.59.511.50
410.59.511.511.5510.6512.9385.71
511.5510.6512.911.5510.6512.9514.29
611.5510.6512.912.4711.1713.71185.71
712.4711.1713.712.4711.1713.71314.29
812.4711.1713.713.3111.9114.61985.71
913.3111.9114.613.3111.9114.62114.29
1013.3111.9114.614.1112.3115.52785.71
1114.1112.3115.514.1112.3115.52914.29
1214.1112.3115.51513.3816.43585.71
131513.3816.41513.3816.43714.29
141513.3816.41614.2517.44385.71
151614.2517.41614.2517.44514.29
16
17Rate:11.17
18Code:PTBB5
19Hours:1185.71
20
VLOOKUP
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
I think you're looking for

=INDEX(H3:H15,MATCH(C17,INDEX(B3:G15,0,MATCH(C18,B2:G2,0))))

Anchor refs as needed.
 

Lifesaver

New Member
Joined
Nov 9, 2004
Messages
8
Thank you guys!

To just_jon:

It work only if I specify H3:H15 instead of H2:H15 in the first argument of the INDEX function. Otherwise, it does not return the correct amount of hours. Do you think this is a correct fix?

=INDEX(H2:H15,MATCH(C17,INDEX(B3:G15,0,MATCH(C18,B2:G2,0))))


To Peter_SSs:

Your solution is great with the only exception that I want to avoid refering to another cell (D18) in the function. If I include it in my function, it becomes rather complex:

=VLOOKUP(C17,OFFSET(B3:B15,,MATCH(C18,B2:G2,0)-1,,8-MATCH(C18,B2:G2,0)),8-MATCH(C18,B2:G2,0),0)

My example was simple, but the reality is that my database contains about 30,000 records by about 25 columns. If there is a way to simplfy it, feel free to let me know.

Thanks anyway to both of you, you're saving me a lot of work by helping me out! Have a great day!
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
If your looking for the 1st Match only, then add a 0 for the match type instead of 1 (or omitted).

=INDEX($H$3:$H$15,MATCH($C$19,INDEX($B$3:$G$15,0,MATCH($C$20,$B$2:$G$2,0)),0))
 

Lifesaver

New Member
Joined
Nov 9, 2004
Messages
8
Thank you very much NBVC! It works nicely! :biggrin:

Many thanks to just_jon and Peter_SSs as well for bringing me to the correct path! :oops:

LifeSaver ...saved!
 

Forum statistics

Threads
1,137,060
Messages
5,679,385
Members
419,824
Latest member
Mercy kiara

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