VLOOKUP & CONCATENATE FROM 3 TABLES INTO 1

[Asher]

Hello,

I have 1 main table(AllTask) for a view of certain information. One row of this table needs to be a ranking of importance of Requests by ID#.

There are 3 tables(SysTest,FSTB,SIM) for different areas that have a say in how they want these requests ranked.

I have a formula that looks up all 3 tables and displays a ranking if there is one:

Code: Select all

=IFERROR(IF(ISNA(VLOOKUP(Table_AllTask[[#This Row],[ID]],SysTest[[#All],[ID]:[Rank]],10,FALSE)),IF(ISNA(VLOOKUP(Table_AllTask[[#This Row],[ID]],FSTB[[#All],[ID]:[Rank]],10,FALSE)),VLOOKUP(Table_AllTask[[#This Row],[ID]],SIM[[#All],[ID]:[Rank]],10,FALSE),VLOOKUP(Table_AllTask[[#This Row],[ID]],FSTB[[#All],[ID]:[Rank]],10,FALSE)),VLOOKUP(Table_AllTask[[#This Row],[ID]],SysTest[[#All],[ID]:[Rank]],10,FALSE)),"")

The problem I am running in to is some Requests might be on more than one priority table. I think I need to somehow concatenate the values if there are more than one, but can't seem to get my head around the logic.

Any ideas?

[HansV]

Try

=IFERROR(VLOOKUP(Table_AllTask[[#This Row],[ID]],SysTest[[#All],[ID]:[Rank]],10,FALSE),"")&"-"&IFERROR(VLOOKUP(Table_AllTask[[#This Row],[ID]],FSTB[[#All],[ID]:[Rank]],10,FALSE),"")&"-"&IFERROR(VLOOKUP(Table_AllTask[[#This Row],[ID]],SIM[[#All],[ID]:[Rank]],10,FALSE),"")

[Asher]

WOW HANS, I was still working on it in the meantime and I came up with this:

Code: Select all

=IFERROR(IF(ISNA(CONCATENATE(VLOOKUP(Table_AllTask[[#This Row],[ID]],SysTest[[#All],[ID]:[Rank]],10,FALSE),VLOOKUP(Table_AllTask[[#This Row],[ID]],FSTB[[#All],[ID]:[Rank]],10,FALSE),VLOOKUP(Table_AllTask[[#This Row],[ID]],SIM[[#All],[ID]:[Rank]],10,FALSE))),IF(ISNA(CONCATENATE(VLOOKUP(Table_AllTask[[#This Row],[ID]],SysTest[[#All],[ID]:[Rank]],10,FALSE),VLOOKUP(Table_AllTask[[#This Row],[ID]],FSTB[[#All],[ID]:[Rank]],10,FALSE))),IF(ISNA(CONCATENATE(VLOOKUP(Table_AllTask[[#This Row],[ID]],SysTest[[#All],[ID]:[Rank]],10,FALSE),VLOOKUP(Table_AllTask[[#This Row],[ID]],SIM[[#All],[ID]:[Rank]],10,FALSE))),IF(ISNA(CONCATENATE(VLOOKUP(Table_AllTask[[#This Row],[ID]],FSTB[[#All],[ID]:[Rank]],10,FALSE),VLOOKUP(Table_AllTask[[#This Row],[ID]],SIM[[#All],[ID]:[Rank]],10,FALSE))),IF(ISNA(VLOOKUP(Table_AllTask[[#This Row],[ID]],SysTest[[#All],[ID]:[Rank]],10,FALSE)),IF(ISNA(VLOOKUP(Table_AllTask[[#This Row],[ID]],FSTB[[#All],[ID]:[Rank]],10,FALSE)),VLOOKUP(Table_AllTask[[#This Row],[ID]],SIM[[#All],[ID]:[Rank]],10,FALSE),VLOOKUP(Table_AllTask[[#This Row],[ID]],FSTB[[#All],[ID]:[Rank]],10,FALSE)),VLOOKUP(Table_AllTask[[#This Row],[ID]],SysTest[[#All],[ID]:[Rank]],10,FALSE)),CONCATENATE(VLOOKUP(Table_AllTask[[#This Row],[ID]],FSTB[[#All],[ID]:[Rank]],10,FALSE),VLOOKUP(Table_AllTask[[#This Row],[ID]],SIM[[#All],[ID]:[Rank]],10,FALSE))),CONCATENATE(VLOOKUP(Table_AllTask[[#This Row],[ID]],SysTest[[#All],[ID]:[Rank]],10,FALSE),VLOOKUP(Table_AllTask[[#This Row],[ID]],SIM[[#All],[ID]:[Rank]],10,FALSE))),CONCATENATE(VLOOKUP(Table_AllTask[[#This Row],[ID]],SysTest[[#All],[ID]:[Rank]],10,FALSE),VLOOKUP(Table_AllTask[[#This Row],[ID]],FSTB[[#All],[ID]:[Rank]],10,FALSE))),CONCATENATE(VLOOKUP(Table_AllTask[[#This Row],[ID]],SysTest[[#All],[ID]:[Rank]],10,FALSE),VLOOKUP(Table_AllTask[[#This Row],[ID]],FSTB[[#All],[ID]:[Rank]],10,FALSE),VLOOKUP(Table_AllTask[[#This Row],[ID]],SIM[[#All],[ID]:[Rank]],10,FALSE))),"")

Yours is soooo way cooler and less bighugeuglyOMGbeastmonster! Thanks sooo much... again! YOU ROCK! Am I supposed to do something to show this is solved? Other boards require that, it improves your stats of awesomeness or something...

[Asher]

I am assuming that my conditional formatting no longer works on this set because it's looking for numbers and now the CONCATENATE hase made it text. Any idea how to make it still turn yellow since the numbers now show up as text when they have a ranking listed?

This is helpful since there are hundreds of requests and only the top 25 are ranked.

[HansV]

How is your conditional formatting set up?

[Asher]

It's set up to color the cells in the column light yellow if they have a value betwen 1 and 25

[HansV]

I fear this is going to be ugly.
Would it be feasible to use three columns, one for each of the lookups from SysTest,FSTB,SIM? That way, you'd still have numbers, and you could set up conditional formatting with a formula that looks at the three columns.

[Asher]

I know, right :-), but that isn't what "they" want. That was what I thought because it was easiest that way (and already done). But it makes the table too big to see all info on one screen even in 80% view with all unneccesary columns hidden.

[HansV]

Try this as formula in conditional formatting:

=OR(VLOOKUP(Table_AllTask[[#This Row],[ID]],SysTest[[#All],[ID]:[Rank]],10,FALSE)<=25,VLOOKUP(Table_AllTask[[#This Row],[ID]],FSTB[[#All],[ID]:[Rank]],10,FALSE)<=25,VLOOKUP(Table_AllTask[[#This Row],[ID]],SIM[[#All],[ID]:[Rank]],10,FALSE)<=25)

[Asher]

Wow, here's something to note:

"You cannot use references to other worksheets or workbooks for Conditional Formatting criteria."

Also, I have found that conditional formatting doesn't read table references, you have to use the cell reference instead of the range-like name that you get from a table. Why? Dunno, seems a big oversight to me. Both of these things do. Unless anyone knows a way around it.

[Asher]

For some reason, it works if I do:

Format only cells with:

Cell Value --> not between --> =""-"" --> and --> 1

[HansV]

Also, I have found that conditional formatting doesn't read table references, you have to use the cell reference instead of the range-like name that you get from a table.

This table stuff is new - apparently they didn't get round yet to implementing it in conditional formatting.

[Asher]

This table stuff is new - apparently they didn't get round yet to implementing it in conditional formatting.

I wonder if they fixed it in 2010...