VLOOKUP multiple columns in a Named Range on a separate sheet

[steve.waye]

Hi,

I have the following table on a spreadsheet...

I need to automatically insert a text string in the "Units" column based on the values in the "Course", "Elective" and "Stage" columns.

I have created the named range "Workplace_Delivery" on a hidden sheet as follows...

I'm using the equation below but I'm getting a #SPILL! error...

=VLOOKUP([Course]&[Elective]&[Stage],Workplace_Delivery,4,FALSE)

Any help would be appreciated.

 

[StephenCrump]

You're getting the #SPILL error because you're looking up entire columns, e.g. [Course] rather than single values [@Course]. You're also concatenating [Course], [Elective] and [Stage] into a single lookup_value but trying to match across multiple columns in Workplace_Delivery.

There are different ways you could do this. Here's one that should work:

=FILTER(INDEX(Workplace_Delivery,,4),([@Course]=INDEX(Workplace_Delivery,,1))*([@Elective]=INDEX(Workplace_Delivery,,2))*([@Stage]=INDEX(Workplace_Delivery,,3)),"Not found")

 

[steve.waye]

Doh. The [Course] verses [@Course] error was an oversight. Stupid mistake. ?

I like what you did with FILTER though. Thanks for the help.