combining vlookup and hlookup to extract data on another sheet

It's easier to use Index-Match for this than V/HLookups. This should do what you want, I think.

1. Go to your 'Full fixture list' sheet and convert the information there to an Excel table, by clicking anywhere in it and pressing Ctrl-T. Excel will automatically select the correct range. Leave the 'my table has headers' selected. This will make it much easier to see what's going on.

2. Rename the table by clicking the 'Table Tools - Design' tab and changing the 'Table1' at the left-hand end to 'T_FixtureList'. (You can call it anything, but this is what I've used for the formula below.

3. Rename the last column to 'Result' instead of 'Column2'.

4. Go to the 'Fixture Grid' sheet. Select the range B2:Y25 and change the cell format from 'Text' to a date format (whichever format you prefer). You must do this before the next step or Excel won't treat the formula as a formula but as text.

5. Put this formula in B2:

Formula:

Please Login or Register  to view this content.

Important: this is an array formula so needs to be entered using Ctrl-Shift-Enter not just Enter. You will know the array is active when you see curly brackets { } appear around your formula - don't try to enter them yourself. If you do not Ctrl-Shift-Enter you will get an error or a clearly incorrect result (in this case, you'll get 'not found' in every cell except the grey ones). If that happens, just click into the formula bar and try C-S-E again.

You can now drag the formula right then down (or down then right, doesn't matter).

There are a few fixtures which don't appear in your fixture list. I think they correspond to some non-standard entries in your fixture list (Milton Keynes instead of MK Dons, etc). I've put 'not found' in the formula above for them - change it to whatever you want. You can use Conditional Formatting to find these 'not found' cells more easily if you like.

Your file is attached with these changes. Hope that does what you want.

Hi, test my formula in C2 and copy to the appropriate remaining cells.
If there is no set of teams, for example: AFC Wimbledon vs Blackpool will appear "???".

Please Login or Register  to view this content.

You're welcome, glad to be of help.