Using names to locate info

So i'm working on a formula to calculate power rankings for high school soccer.

So i'm wondering if there is a way that you can type in the school's name and have it's information calculate into a desired cell.

Ex:

School A is playing school B.

I need school B's win% which is located on the same number(different letter) to appear on school A's cell that shows their match and vice versa.

So school A will have a list of each school it plays throughout the year. I need to be able to type in their names and have their info show up with out me having to hunt it down. It's over 3000 cells worth of info. Can be an overload at times.

please let me know if this is possible.

Hi,

can you please upload a workbook with your data layout and a small data sample. It will be very easy once we can see the workbook structure.

cheers

click the button "Go Advanced" below and then click on the paperclip icon. Make sure your sample workbook is not too big. I'm on a data plan

Yes it is.

In column E you need this formula

=OFFSET($A$1,MATCH(B2,$A$1:$A$100,0),20)

In column F you need this one

=OFFSET($A$1,MATCH(B2,$A$1:$A$100,0),21)

There are hidden columns in the sheet, which may make the offset a little less obvious (stunned me for a brief moment). You need to adjust the $A$100 in the MATCH formula to suit the number or rows on your worksheet.

Hope that's what you're after

Hi again,

the only thing you have to change in the formula is the blue bit

this is the formula in column F2
=OFFSET($A$1,MATCH(B2,$A$1:$A$100,0),21)

Set this number to the row number of the last school's data. Do that for the formula in E2 as well.

Then do this: select E1 to F2, click the fill handle in the lower right corner of the selection and drag it down the list to the last school.

Done.