Lookup multiple worksheets in a single call

[Shane]

Hi there!

I have three worksheets for my golf sociey:
Outing1
Outing2
Outing3
Each worksheet contains points won by each player in each event. On worksheet "Outing3", I want to lookup the points accumulated by each player to dat. This I can do.

My problem is where a player played in outing1 but missed outing2. The lookup in outing3 will return the result "#N/A" as it finds nothing in worksheet "outing2".

Where the lookup finds "#N/A", I need it to lookup the previous outing to search for a result. How do I do this? I imagine it uses a coctail of "if", "vlookup" and "isnumber" statements.

Note that although I've used 3 workbooks for illustration, the actual file contains worksheets for 7 outings.

Thanks for any help you can give!

[Anne Troy]

Just before your vlookup, do this IF

=If(ISNA(VLOOKUPFORMULA),True,VLOOKUPFORMULA)

True = What you want it to do if it IS returning #N/A

[Aladin Akyurek]

On 2002-05-10 08:48, Shane wrote:
Hi there!

I have three worksheets for my golf sociey:
Outing1
Outing2
Outing3
Each worksheet contains points won by each player in each event. On worksheet "Outing3", I want to lookup the points accumulated by each player to dat. This I can do.

My problem is where a player played in outing1 but missed outing2. The lookup in outing3 will return the result "#N/A" as it finds nothing in worksheet "outing2".

Where the lookup finds "#N/A", I need it to lookup the previous outing to search for a result. How do I do this? I imagine it uses a coctail of "if", "vlookup" and "isnumber" statements.

Note that although I've used 3 workbooks for illustration, the actual file contains worksheets for 7 outings.

Thanks for any help you can give!

Where the lookup finds "#N/A", I need it to lookup the previous outing to search for a result. How do I do this?

This is a bit unclear (to me). It looks like you're looking for a value in the latest "Outing" first. If that's not successfull,then you want to look in the one before the latest. Any comments?

[Shane]

Aladin, your interpretation is exactly correct (sorry if I was not clear enough).

Dreamboat, I'm going to check out your suggestion now...

[Shane]

Dreamboat, your solution works like a dream (excuse the sad pun!) for the 3 outings noted in my query.

To finish this solution off, you'll remember I mentioned that there are in fact 7 outings in total. In the worksheet for outing7, I'll need your formula to refernce back to the previous outing, moving back as far as outing1 where "#N/A" is returned in later outings.

How would you propose I structure your formula to accommodate these additional lookups?

Thanks!

[Aladin Akyurek]

On 2002-05-12 04:12, Shane wrote:
Dreamboat, your solution works like a dream (excuse the sad pun!) for the 3 outings noted in my query.

To finish this solution off, you'll remember I mentioned that there are in fact 7 outings in total. In the worksheet for outing7, I'll need your formula to refernce back to the previous outing, moving back as far as outing1 where "#N/A" is returned in later outings.

How would you propose I structure your formula to accommodate these additional lookups?

Thanks!

I don't belive that would be doable, given at least 7 lookup tables that you have to consider.

Probably a VBA code that goes thru all your tables would be more effective.

A possible formula-based approach consists of naming all tables one by one like Outing1, Outing2, etc.

In an Overview sheet you might consider a layout as shown in the figure below:

Microsoft Excel - aaIterativeLookup Shane.xls
File(F) Edit(E) View(V) Insert(I) Options(O) Tools(T) Data(D) Window(W) Help(H)
A1		=	Player
*	A	B	C	D	E	F	G
1	Player	Latest Score	Outing5	Outing4	Outing3	Outing2	Outing1
2	john	:alert('{=IF(COUNT(C2:G2),OFFSET(C2,0,MIN(IF(ISNUMBER(C2:G2)*COLUMN(C2:G2),ISNUMBER(C2:G2)*COLUMN(C2:G2)) )-3,1,1),"")}')>10	:alert('=VLOOKUP($A2,INDIRECT(C$1),2,0)')>10	:alert('=VLOOKUP($A2,INDIRECT(D$1),2,0)')>#N/A	:alert('=VLOOKUP($A2,INDIRECT(E$1),2,0)')>8	:alert('=VLOOKUP($A2,INDIRECT(F$1),2,0)')>12	:alert('=VLOOKUP($A2,INDIRECT(G$1),2,0)')>6
3	eric	:alert('{=IF(COUNT(C3:G3),OFFSET(C3,0,MIN(IF(ISNUMBER(C3:G3)*COLUMN(C3:G3),ISNUMBER(C3:G3)*COLUMN(C3:G3)) )-3,1,1),"")}')>9	:alert('=VLOOKUP($A3,INDIRECT(C$1),2,0)')>#N/A	:alert('=VLOOKUP($A3,INDIRECT(D$1),2,0)')>9	:alert('=VLOOKUP($A3,INDIRECT(E$1),2,0)')>12	:alert('=VLOOKUP($A3,INDIRECT(F$1),2,0)')>#N/A	:alert('=VLOOKUP($A3,INDIRECT(G$1),2,0)')>9
4	damon	:alert('{=IF(COUNT(C4:G4),OFFSET(C4,0,MIN(IF(ISNUMBER(C4:G4)*COLUMN(C4:G4),ISNUMBER(C4:G4)*COLUMN(C4:G4)) )-3,1,1),"")}')>5	:alert('=VLOOKUP($A4,INDIRECT(C$1),2,0)')>#N/A	:alert('=VLOOKUP($A4,INDIRECT(D$1),2,0)')>5	:alert('=VLOOKUP($A4,INDIRECT(E$1),2,0)')>10	:alert('=VLOOKUP($A4,INDIRECT(F$1),2,0)')>7	:alert('=VLOOKUP($A4,INDIRECT(G$1),2,0)')>5
5	aladin	:alert('{=IF(COUNT(C5:G5),OFFSET(C5,0,MIN(IF(ISNUMBER(C5:G5)*COLUMN(C5:G5),ISNUMBER(C5:G5)*COLUMN(C5:G5)) )-3,1,1),"")}')>*	:alert('=VLOOKUP($A5,INDIRECT(C$1),2,0)')>#N/A	:alert('=VLOOKUP($A5,INDIRECT(D$1),2,0)')>#N/A	:alert('=VLOOKUP($A5,INDIRECT(E$1),2,0)')>#N/A	:alert('=VLOOKUP($A5,INDIRECT(F$1),2,0)')>#N/A	:alert('=VLOOKUP($A5,INDIRECT(G$1),2,0)')>#N/A
Overview

You can see the formula of cells only click each above hyperlinks

The above image was automatically written by HtmlMakerVer1.12
If you want this code, click here and Colo will email the file to you.


You can hide the colored columns.

B2 houses the following array-formula which is copied downwards:

=IF(COUNT(C2:G2),OFFSET(C2,0,MIN(IF(ISNUMBER(C2:G2)*COLUMN(C2:G2),ISNUMBER(C2:G2)*COLUMN(C2:G2)))-3,1,1),"")

C2 houses a VLOOKUP formula which is copied across then down:

=VLOOKUP($A2,INDIRECT(C$1),2,0)

C1 to G1 houses the table names from the latest to the earliest.

Aladin

[Shane]

My workbook is fully functional now. Thanks very much guys!