Excel - Inserting a variable in VLOOKUP

  howryou 17:21 16 May 08
Locked

Starting with =VLOOKUP(G8,XXXXX,N5,FALSE)/100, where XXXXX is the name of the range, which is the result of a concatenation of "Year", the contents of Cell M5 & the contents of Cell O5
=CONCATENATE("Year",M5,O5) - can it be done?

  VoG II 17:30 16 May 08

What would you expect XXXXX to evaluate to?

  VoG II 10:38 17 May 08

Let us assume that you want the equivalent of

=VLOOKUP(G8,Year!A1:C20,N5,FALSE)

M5 houses A1

O5 houses C20

Formula:

=VLOOKUP(G8,INDIRECT("Year!"&M5&":"&O5),N5,FALSE)

  howryou 08:58 20 May 08

Apologies - away for the weekend!

The XXXXX would be created from =CONCATENATE("Year",M5,O5) and this would generate a number of possibilities from "YearA4" to "YearA9" and "YearB1" to "YearB9", each of which are already defined Names/Areas.

How would you effectively get the Concatenation into the VLOOKUP

=VLOOKUP(G8,(CONCATENATE("Year",M5,O5)),N5,FALSE)/100

  VoG II 09:15 20 May 08

Untested but try

=VLOOKUP(G8,INDIRECT(CONCATENATE("Year",M5,O5)),N5,FALSE)/100

  howryou 10:20 20 May 08

Certainly works for me - Many thanks.

This thread is now locked and can not be replied to.

Sniper Elite 4 review: Headshotting Nazis has never felt so good

1995-2015: How technology has changed the world in 20 years

The Best Design, Illustration, Animation and VFX Awards of 2017

WWDC 2017 dates: How to get WWDC 2017 tickets, when is WWDC 2017 and more details announced