Lookup Value

[DaveM]

I want to retrieve a value from a table on another worksheet. The table has 6 columns of information and is quite large (b3:g2500). I want to retrieve the value in the 6th column when the first 5 match the columns on the 2nd worksheet (b8:g8).

Example - Source table
EMP HOP SCALE CED GNG 35
EMP HOP SCALE SPR GNG 50
WC VER CAMP CED MER 25
WC HAN SCALE CED GNG 50
WC HAN SCALE CYP MKZ 47

The value desired should come from row:
WC HAN SCALE CED GNG

I have tried Vlookup, index/match, and even CSE (which did not seem to work...the brackets wouldn't turn on when I puched in Ctrl-Shift_Enter). The general result of my efforts has been #VALUE.

I would prefer a formula over a macro.

Any help is greatly appreciated.

PS this is my first time on the site and I have already learned some really cool stuff.

Thanks

[Aladin Akyurek]

On 2002-05-25 08:25, DaveM wrote:
I want to retrieve a value from a table on another worksheet. The table has 6 columns of information and is quite large (b3:g2500). I want to retrieve the value in the 6th column when the first 5 match the columns on the 2nd worksheet (b8:g8).

Example - Source table
EMP HOP SCALE CED GNG 35
EMP HOP SCALE SPR GNG 50
WC VER CAMP CED MER 25
WC HAN SCALE CED GNG 50
WC HAN SCALE CYP MKZ 47

The value desired should come from row:
WC HAN SCALE CED GNG

I have tried Vlookup, index/match, and even CSE (which did not seem to work...the brackets wouldn't turn on when I puched in Ctrl-Shift_Enter). The general result of my efforts has been #VALUE.

I would prefer a formula over a macro.

Any help is greatly appreciated.

PS this is my first time on the site and I have already learned some really cool stuff.

Thanks

See my contrib on multikey lookup in the thread

http://www.mrexcel.com/board/viewtop...c=9256&forum=2