Search & Match Formula Help

[Taff]

Morning all,

really struggling with this one, so would appreciate any guidance/help.

I maintain a soccer results work book, which means i need to manually check a database for the weekly results, and then maaually enter them into required worksheet. Very time consuming and tiring.

Each work sheet is called; E0, E1, I1, I2, D1, D2 etc dependent upon country

Each required result is in a worksheet called either Check, LTD, XGOA

The common format is as below:

Col A B C D
16/12/2018 D2 Ingolstadt Heidenheim
17/12/2018 I2 Verona Pescara
21/12/2018 F2 Niort Brest
22/12/2018 E1 Blackburn Norwich
22/12/2018 E2 Plymouth Accrington
22/12/2018 E2 ShrewsburyCoventry
22/12/2018 I1 Milan Fiorentina
22/12/2018 SP2 Cordoba Las Palmas
23/12/2018 I2 SalernitanaFoggia


I am looking for some code/help/formula that would:
Look in worksheet "D2" for the date of 16/12/2018, and game Inglostadt v Heidenhelm, then take the result which is Col F and Col G and enter it alongside the searched criteria in worksheet "Check". then repeat - looking in worksheet "I2" for Verona v Pescara etc

Hopefully this makes sense?

Cheers as always

Taff

 

[etaf]

you can probably use an indirect to lookup the results
BUT
how do you know what worksheet to look in
Check, LTD, XGOA

Where are the results to be displayed in worksheet "check" - then why are you also looking up in worksheet "check"

there are quite a few ways to lookup 2 values and return the result in another sheet

Column A tells you what date to lookup
column B tells you what sheet to look in - is that correct (then this can be used in an indirect() for the lookup
column c is the match - and the way it is spelt in that column is exactly the same as in the other sheets
for example
Ingolstadt Heidenheim
is not
Ingolstadt v Heidenheim
in the other sheets ?

so you are matching the value in column A & column C
and using column B to say which sheet to lookup the result

 

[Taff]

The results will always be in either one of the following Worksheets: E0, E1, I1, I2, D1, D2 etc the answers/figures required are always in Col F&G

These results need to be recorded in worksheet "check" column AJ & AK. WorkSheet "check" already has Columns A: D pre-polutated with

16/12/2018 D2 Ingolstadt Heidenheim

apologies:
Col C = Inglostadt
Col D = Heidenhelm
there is no 'v'

spelling etc will always be the same, due to some previous checks & controls....

 

[etaf]

ok,
so in principle then
=INDEX('worksheet from value in B'!$F$2:$F$100,MATCH(A2&C2&D2,INDEX('worksheet from value in B'!$A$2:$A$100&'worksheet from value in B'!$C$2:$C$100&'worksheet from value in B'!$D$2:$D$100,0),0),0)

now we just use an indirect() for the sheet name
does that formula look like its pulling information from the correct cells
if you replace
worksheet from value in B
with either E0, E1, I1 etc
which can be found in the worksheet column B you are returning the results to

BUT before building a sample sheet

Why would the worksheets labeled
E0, E1, I1, I2, D1, D2
Have the sheet value in column B
so E0 sheet
has
date / E0 / team a / team B

I think it would save a lot of time, if you put a sample sheet onto a share like dropbox, onedrive or icloud - so we can be sure of the formatted data and sheets
otherwise i may waste a lot of time guessing

 

[Taff]

Yes it does hold the value in Col B, its just the way the initial data is imported, also used for another project.

 

[Taff]

Ok so i messed up and realised i was referencing some wrong info for the results, ie wrong column info - sorry. But you have helped me get 75% there THANK YOU!

the following works:
=INDEX('P1'!$F$2:$F$5000,MATCH(A2&C2&D2,INDEX('P1'!$C$2:$C$5000&'P1'!$D$2:$D$5000&'P1'!$E$2:$E$5000,0),0),0)

But I need the 'P1'! to be automatically whatever is in B2, (which says which worksheet has the result) otherwise i need to manually input the worksheet.

Any suggestions or hints?

 

[DanteAmor]

Try this

<b>Sheet: D2</b>

Excel Workbook
	C	D	E	F	G
1	DATE	TEAM1	TEAM2	R1	R2
2	16/12/2018	Ingolstadt	Heidenheim	1	3
Sheet

<b>Sheet: I2</b>

Excel Workbook
	C	D	E	F	G
1	DATE	TEAM1	TEAM2	R1	R2
2	17/12/2018	Verona	Pescara	5	4
Sheet

<b>Sheet: Check</b>

Excel Workbook
	A	B	C	D	AJ	AK
1	DATE	SHEET	TEAM1	TEAM2	R1	R2
2	16/12/2018	D2	Ingolstadt	Heidenheim	1	3
3	17/12/2018	I2	Verona	Pescara	5	4
Sheet