Look up to the left with multiple worksheets

[donnans]

I have about 20 worksheets with data in similar format. I have VLOOKUP working fine with multiple worksheets in a SheetList, to bring selected information into a new worksheet. But this won't work looking left in (from) the array. I can search one worksheet by Index-Match but I do not know how to use the SheetList in the index and match functions to search all the worksheets at once, and I cannot find an answer anywhere.


This works fine (my personal arrays)
=VLOOKUP([FONT=&quot]$C7[/FONT],INDIRECT[FONT=&quot]([/FONT]"'"&INDEX[FONT=&quot]([/FONT][FONT=&quot]SheetList[/FONT],MATCH[FONT=&quot]([/FONT]1,--[FONT=&quot]([/FONT]COUNTIF[FONT=&quot]([/FONT]INDIRECT[FONT=&quot]([/FONT]"'"&[FONT=&quot]SheetList[/FONT]&"'!$d$6:$e$200"[FONT=&quot])[/FONT],[FONT=&quot]$C7[/FONT][FONT=&quot])[/FONT]>0[FONT=&quot])[/FONT],0[FONT=&quot])[/FONT][FONT=&quot])[/FONT]&"'!$d$6:$e$200"[FONT=&quot])[/FONT],2,0)
This gets the amount of an invoice where the invoice numbers are in a 'Fill Series" list on a new spreadsheet.


This works for a single worksheet:
=INDEX(Railway!$B$6:$B$25,MATCH[FONT=&quot]([/FONT][FONT=&quot]C18[/FONT],Railway!$D$6:$D$25,0[FONT=&quot])[/FONT])
This gets the date of the invoice which is in a column to the left of the invoice number in each spreadsheet. Changing the column order makes no sense and would be awkward for the initial data entry. But this is only one selected worksheet to test if the index-match formula works - it does.
[FONT=&quot]
[/FONT]
[FONT=&quot]Can someone advise about how to incorporate the SheetList (a named array of all the worksheet names) into INDEX and into MATCH.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Thanks[/FONT]

 

[FDibbins]

See if you can adapt this to your needs?

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr]
[tr][td]

2​

[/td][td]VLOOKUP backwards[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td]solution by abousetta[/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]

Amount​

[/td][td]

Listed​

[/td][td]

Company​

[/td][td][/td][td]Company[/td][td]

Amount​

[/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

20,000​

[/td][td]

Yes​

[/td][td]

A​

[/td][td][/td][td]

B​

[/td][td]

13000​

[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]

13,000​

[/td][td]

Yes​

[/td][td]

B​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]

18,000​

[/td][td]

No​

[/td][td]

C​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
F5=VLOOKUP(E5,CHOOSE({1,2},C5:C7,A5:A7),2,0)

Or, you could just build the INDIRECT into your INDEX/MATCH function

 

[donnans]

Thanks FDibbins - as I said I can get the INDEX-MATCH formula to work with a single spreadsheet.
I realised that I needed the INDIRECT approach but I couldn't manage to get all the syntax correct. INDIRECT seems to need the array name SheetList to be referred to differently for INDEX and for MATCH.

I would be very grateful if you could show me how to incorporate INDIRECT in the following, where instead of the worksheet name Railway the full set of worksheet names is in the named array SheetList in the same worksheet as the cells in which the result of the INDEX-MATCH formula re located. All the worksheet formats are compatible.

[FONT=&quot]=INDEX(Railway!$B$6:$B$25,MATCH[/FONT](C18[FONT=&quot],Railway!$D$6:$D$25,0[/FONT])[FONT=&quot])

Thanks[/FONT]

 

[Aladin Akyurek]

In D7 control+shift+enter, not just enter:

=IF(ISNUMBER($E7),INDEX(INDIRECT("'"&INDEX(SheetList,$E7)&"'!B6:B25"),MATCH($C7,INDIRECT("'"&INDEX(SheetList,$E7)&"'!D6:D25"),0)),"Not Found")

In E7 control+shift+enter, not just enter:

=IFERROR(MATCH(TRUE,COUNTIFS(INDIRECT("'"&SheetList&"'!D6:D25"),$C7)>0,0),"")

 

[donnans]

Many thanks Aladin Akyurek. The formulae work (although I have a few problems with some sheet names only being recognised some of the time - a gremlin at my end, I'm sure).
It's unexpected to need a two stage process - I had assumed it was possible to insert the INDIRECT aspect into the INDEX-MATCH formula. Anyway, it is acceptable to have an extra column in the 'output' worksheet if it doesn't involve any changes to the original data entry spreadsheets.
If anyone else can manage a single step indirect index match we would all be interested and impressed.

Stuart

 

[Aladin Akyurek]

Many thanks Aladin Akyurek. The formulae work (although I have a few problems with some sheet names only being recognised some of the time - a gremlin at my end, I'm sure).

I'm not going to chase after your gremlins however nice or evil. As you must know, SheetList stands for a range that houses the relevant sheet names. If those are typed correctly, the formula won't have any trouble.

It's unexpected to need a two stage process - I had assumed it was possible to insert the INDIRECT aspect into the INDEX-MATCH formula. Anyway, it is acceptable to have an extra column in the 'output' worksheet if it doesn't involve any changes to the original data entry spreadsheets.
If anyone else can manage a single step indirect index match we would all be interested and impressed.

Stuart

For reasons of efficiency. Otherwise it is just computing the same thing three times... Just a simple substitution will impress you...

=IF(ISNUMBER(MATCH(TRUE,COUNTIFS(INDIRECT("'"&SheetList&"'!D6:D25"),$C7)>0,0)),INDEX(INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIFS(INDIRECT("'"&SheetList&"'!D6:D25"),$C7)>0,0))&"'!B6:B25"),MATCH($C7,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIFS(INDIRECT("'"&SheetList&"'!D6:D25"),$C7)>0,0))&"'!D6:D25"),0)),"Not Found")

which needs to be confirmed with control+shift+enter, not just enter.

 

[donnans]

Thanks again Aladin Akyurek - I am indeed impressed!

My comment about the gremlins wasn't a complaint about your formula, of course. I have sorted the gremlins (precise array size in the full set of worksheets rather than in one example) myself and didn't mean to imply that I thought you might.

I can see what you mean by efficiency but I'm trying to have as few additional columns as possible on a shared spreadsheet where one user could accidentally think that a column (e.g. the number of the relevant worksheet in SheetList which you put in column E) was redundant. Anyway, that's an admin problem not a coding problem.

But I could have said earlier that in the 'summary' worksheet into which these codes are inserted I already have a column with the client name which is in fact the worksheet name picked up from a VLOOK function using SheetList. Could I use that information to reduce the three-step INDIRECT-INDEX-MATCH formula to two-step, or would it just mean changing the code of one or more of the three steps (which would seem pointless)?

I very much appreciate your time and expertise.

 

[Aladin Akyurek]

@donnans

You are welcome. Thanks for the kind and thoughtful update.