Need advice on excel formula

Question

0.00/5 (No votes)

See more:

Hi all,
I am facing a problem with Excel formula.
I have an Excel WorkBook which play with steel beams.
I have a set named ranges, all with same structure: ref_ipe, ref_ipea, ref_hea, ref_heb, ref_upn, ref_upe ...
All nice so far.

I have this kind of formula where I need to switch named ranges depending on user inputs:

=VLOOKUP(B287,IF(A287="IPE",ref_ipe,IF(A287="HEA",ref_hea,IF(A287="UPN",ref_upn,IF(A287="TUBE",ref_tubec,ref_l)))),2,FALSE))

This formula works and is fast, but my problem is that I am asked to add more named ranges, and it is where it become a pain to add more and more nested IFs.
As an alternative I have done this:

=VLOOKUP(B23,INDIRECT(VLOOKUP(A23,{"IPE","ref_ipe";"IPEA","ref_ipea";"HEA","ref_hea"},2,FALSE)),2,FALSE)

which is neat to extend with more ranges, but since INDIRECT is volatile, my sheet is downgrade quickly as I have thousands of formulas to change.

What I have tried:

I have tried

=VLOOKUP(B23,VLOOKUP(A23,{"IPE",ref_ipe;"IPEA",ref_ipea;"HEA",ref_hea},2,FALSE),2,FALSE)

but excel don't want it, because ~~VLOOKUP can't return a range~~ constant musts with named ranges are not allowed.

Do you know a better solution ?

Nota: formulas may contain errors as they are translated from French Excel.

Posted 14-Nov-17 6:33am

Patrice T

Updated 15-Nov-17 4:00am

v8

Add a Solution

Comments

CHill60 14-Nov-17 15:03pm

Could you copy relevant data into a single named range based on the Worksheet.Change event? You need @maciej-los on this :-)

Patrice T 14-Nov-17 15:21pm

Unfortunately, I have some other constraints that prevent me from doing it.

CHill60 14-Nov-17 15:23pm

I'll keep thinking. That's why I used a comment rather than a solution :-)

CHill60 14-Nov-17 15:28pm

I'm thinking of something using INDEX maybe, getting away from the multiple named ranges ... sort of ranges within a range. I've seen something like it before but I'm struggling to reproduce

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mehdi Gholam · Accepted Answer · 2017-11-14T21:18:00

Solution 1

I would create a column for each IF() and VLOOKUP() the one with a value (with possibly an intermediary of those columns concatenated together)

Posted 14-Nov-17 21:18pm

Mehdi Gholam

Comments

Patrice T 15-Nov-17 3:41am

Hi Mehdi
I don't see what you mean.

Mehdi Gholam 15-Nov-17 3:48am

The excel model of working is you create more columns, i.e. instead of writing a big formula, break up that formula into column parts and "add" them together into another column.

Patrice T 15-Nov-17 9:46am

Thanks for your assistance.
I think I got the solution.

CPallini 15-Nov-17 4:21am

5. Yes, it would make it simpler.

Mehdi Gholam 15-Nov-17 4:50am

Thanks!

Patrice T · Accepted Answer · 2017-11-15T03:38:00

Solution 2

I think I got it

=VLOOKUP(B23,
CHOOSE(MATCH(A23,{"IPE","IPEA","HEA"},0),ref_ipe,ref_ipea,ref_hea),
2,FALSE)

A MATCH to get rank of user input and a CHOOSE to select the range.

Posted 15-Nov-17 3:38am

Patrice T

Comments

Mehdi Gholam 15-Nov-17 9:49am

5ed, Good to know

Patrice T 15-Nov-17 9:58am

Thank you.
Indeed, I spent hours on this problem :)

CHill60 15-Nov-17 10:04am

Nice. 5'd

Patrice T 15-Nov-17 10:23am

Thank you