conditional statement with multiple Vlookups

[jennag]

Hi all,

I found some questions close to this, but nothing exact.

I have a value that I want to lookup in a table and return an adjacent value (vlookup) but if the value is not found in that table, look in another table, and if not found in that table, look in another. I have five tables I want to search and if it isn't found in any of the tables, return a constant. The number to look-up may be in the previous table, so it needs to search in order, as the number (numbers competitors in a competition in this case) get eliminated as the competition moves on and it needs to return the last round the competitor is found in.

This will work with one table: =IF(ISNA(VLOOKUP(C3,'Table1'!$P$7:$R$12,3,FALSE)),"Not Found",VLOOKUP(C3,'Table1'!$P$7:$R$12,3,FALSE))

This will search the first table, and if not found, will result in "Not Found"

I started with this, but I have not been successful at adding additional conditional (if) statements to it to search additional tables.

Please help!
Thanks,
Jenna

 

[SkipVought]

Hi,

I have five tables I want to search

This is a HORRIBLE design! Similar data ought to be in a single table. That is how Excel is designed to work most effectively.

First, combine your data.

Then your lookup will be a piece of cake!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[jennag]

Yes, I know how to do this in a single tables.

Its not a horrible design, it is the design of the event.

For this instance, tables get smaller and participants (and their data) get eliminated after each round and hence a new table with just the needed data. I am trying to avoid building ANOTHER table (this spreadsheet is huge already).

Jenna

 

[jennag]

Not to mention the fact that if I combine the data, the same data (participant number) will be replicated in the table for each round the particiapnts qualifies for. It will be challenging to have Excel determine which round to return in a lookup.
Jenna

 

[SkipVought]

[tt]
Event Name
1 Al
1 Bob
1 Cal
2 Bob
2 Cal
[/tt]


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Jenna: don't take it personally. But this is, indeed, a horrible design.

As Skip just alluded to, you should add a column that identifies whatever the difference is between the tables. In this case, Round#.

And telling excel which round to pull data for will be dead simple. You may have to use SumProduct instead of VLookup, but it will still be [!]much[/!] more easily achieved than with the design your using.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Sorry, I did not explain how this might work for you.
[tt]
A B
Round ID
1 1
1 2
1 3
2 2
2 3
[/tt]
Last round for each ID
[tt]
D E
ID LastRound
1 =COUNTIF(ID,D2)
2
3
[/tt]
using named ranges for the data in columns A & B.



Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[jennag]

Countif wouldn't work because it is possible for a participants ID # to be there the same amount of times as someone else who made it to a further round (finals and consultation).

I gave in, built a separate table where the participant ID is in column 1 and round eliminated in column 2. The ID closest to the top is the latter round, so vlookup will find the correct reference.
Jenna

 

[SkipVought]

Countif wouldn't work because it is possible for a participants ID # to be there the same amount of times as someone else who made it to a further round (finals and consultation).

How is that?

Please post some sample data and explaination, that would represent this condition.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PeterMoran]

Hi jennag,

Here is the code you requested. I have tested it and it seems to be working ok.

=IF(ISNA(VLOOKUP(C3,Table1!$P$7:$R$12,3,FALSE)), IF(ISNA(VLOOKUP(C3,Table2!$P$7:$R$12,3,FALSE)), IF(ISNA(VLOOKUP(C3,Table3!$P$7:$R$12,3,FALSE)), IF(ISNA(VLOOKUP(C3,Table4!$P$7:$R$12,3,FALSE)), IF(ISNA(VLOOKUP(C3,Table5!$P$7:$R$12,3,FALSE)),"Not Found", VLOOKUP(C3,Table5!$P$7:$R$12,3,FALSE)), VLOOKUP(C3,Table4!$P$7:$R$12,3,FALSE)), VLOOKUP(C3,Table3!$P$7:$R$12,3,FALSE)), VLOOKUP(C3,Table2!$P$7:$R$12,3,FALSE)), VLOOKUP(C3,Table1!$P$7:$R$12,3,FALSE))

I have assumed that Table1 etc. are separate sheets in the one workbook, and in this case the single apostrophes did not appear necessary.

Good Luck!

Peter Moran

 

[jennag]

All of the tables are actually on the same sheet so they can be displayed for spectators and participants. It is a set of competition brackets. I broke it down into a few steps and got it to work.
Jenna

 

[SkipVought]

BTW,

The individual results for each round, can easily be generated from a composite table.

Going the other way, is more difficult as each round increases.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}