Formula to lookup data in selected table without nested IFs

Hi,
Does anyone have any ideas for the following:
I have a formula that uses quite a few IFs all lined up, and I'd like to simplify it a bit, also I think there is a limit to how many IFs I can use.

What the formula does is use the input from C2 to decide which table to VLOOKUP and find the data specified in A2, as long as there is text in A2.. Here's what I have:

Formula:

Please Login or Register  to view this content.

What I'm worried about is I'm going to have to add more clients, and therefore more tables, and I'm going to run out of functionality. I've already got a pretty long formula.

I've attached an example spreadsheet. The variable data cells (A2 and C2) are validated by drop-down lists for convenience, the formula lives at B2, and the tables are located on Sheet2. The data validation named ranges are hidden in Sheet2, J:K, if you need them.

I'm not against doing this with code if I have to.

Thanks for your help.

Example.xlsx

Take a look at the attached and see if you can work with that? i removed the individual tables and combined them into 1, which can now grow. then I used index/match to find the required into.

adjust the ranges as needed.

I should write a book called: "While I was typing"

So, while I was typing, FD came along with something similar to what I was doing. As I've done the work, I might as well post it, as some may prefer the approach of including an IF function as part of the MATCH argument. (In describing what I've done, I've also described how this works, but if any further clarification is required, please just ask.)

Hope this helps, and season's greets and peace to all.

lol sorry BB, but i think i could contribute to that book as well

nice solution did you take a look at mine, its a bit simpler

Come on, so - if we get cracking, we can have it out in time for the new year!

I think there's a time & a place for RIGHT (LEFT, etc) skulduggery, but this isn't it - although there's every chance that if I were to use it repeatedly over a few weeks, I might change my mind. Familiarity breeds a new box of tricks for problem-solving, in this case, and not contempt!

Hi Jaron,

Thanks for the feedback. For what it's worth, array formulas are really nothing to fear, but of course whatever works, works.

All the best,