Thread: INDEX several named tables to return a specific value in the table

Good Morning all!

I got so much help on this worksheet from this forum from this thread

http://www.excelforum.com/excel-work...ml#post2370007

Now i'm working on phase two.

Please keep in mind that I'm kind of an excel beginner, and I'm hacking this thing together using forums like this.

What I'm looking to do on this updated document is fill out the Reverse Lookup Table with a cable number from any of the ZONE Clients lists and have it return the cable number from the supplied tables.

I was able to get it to return the values if you pre-defined the client and cable tables using this formula:

=IF(ISBLANK(F7),"Enter Cable # -->",IF(ISNA(INDEX(PUBLICVLAN,MATCH(F7,LIST_PUBLIC_CBL,0))),"No Match Found!",(INDEX(PUBLICVLAN,MATCH(F7,LIST_PUBLIC_CBL,0)))))

But i need this formula to be able to search the entire client/cable lists and return the appropriate value.

I've tried variations on this formula to no help

=IF(ISBLANK(F8),"Enter Cable # -->",IF(ISNA(INDEX(Clients_Cable_Table,MATCH(F8,Clients_Cable_Table,0))),"No Match Found!",(INDEX(Clients_Cable_Table,MATCH(F8,Clients_Cable_Table,0)))))

I also tried to reverse engineer the indirect functions already included in the attached spreadsheet, but that didn't work out so well either.

Please take a look and thanks in advance!

Originally Posted by Richard Buttrey

Hi,

There seems to be an inconsistency in your E7:E8 formula.
E7 which does work uses PUBLIC_VLAN, which is defined as A25:A30 and LIST_PUBLIC_CBL which is defined as B25:B30

E8 uses Clients_Cable_Table. defined as A24:F32 and since the pub-00001 doesn't exits in A24:A32 it correctly returns the value No Match Found.

HTH

Yes, that's true.

I was trying something out on the second line, trying to see if i could get it to reference the larger table "clients_cable_table" and not just the PUBLIC_VLAN lists.

Also, pub-00001 does exist between a24:f32, it happens to be in the B column.

So what i want to do is type a cable number, and have the function look for that value via the INDEX and return the value from the cell directly to the left.

Bump.......

Hi

On a quick view, it would look as though you could use the prefix part of the cable number (ie the part before the - ) and use a lookup table to convert that to the required named range. Then you could use INDIRECT to determine the defined name to use. Rest of the formula should flow from there.

rylo

Originally Posted by rylo

Hi

On a quick view, it would look as though you could use the prefix part of the cable number (ie the part before the - ) and use a lookup table to convert that to the required named range. Then you could use INDIRECT to determine the defined name to use. Rest of the formula should flow from there.

rylo

I see what you're saying concerning the prefix, example PUB or Z5 or something, but these values are extremely variable. the purpose of the survey sheet is to hand to an existing client that has their own cable naming conventions and allow them to enter their own values. There may not be a "prefix" like PUB or ZONE5

So i need a function that will just look at the cable number, look through all of the columns for that cable number and return the computer name that is to the left of that cell.

I have named the regions of the computer names, and the cable numbers all seperatly

so i have created a table on that sheet called CableTable. that has all of the values of each of those columns...

And again, I'm really kind of a newbie to INDEX and LOOKUP values so any hints on how to approach the formula (with as much detail in plain English as possible) would be greatly appreciated.

Thanks!

OK, another approach.

As you have fixed ranges for all these, even tho it is a template that can be filled in by clients, then just build another lookup table that is based on these fixed ranges. So say your lookup table is going into columns K and L. K1 will point to B27, K2 will point to B28, K3 will point to B29, K4 will point to D27 etc. Then L1 will point to the relevant Server cell (A27, A28, A29, C27...)

That way it won't matter what naming convention your client uses, you will have a fixed lookup table that will be able to reverse the Cable number selection. It would also give you the ability to fix the Cable number selection options (using a list), so you wouldn't have to worry about making a non valid selection. Of course, all this could be put onto a hidden sheet if required, so the client (and/or their users) doesn't have to see it.

rylo