Formula not returning what I think it should

**kingsolo** · 12-31-2006, 05:00 PM

Okay, I'm attaching a screen shot that shows all the info... formula for the cell that I am looking for. The formula should take the info from validation lists in R13 & S13, and match them up in F3:O3 (T1Q2) & C6:C15 (T2Q2) and return the data in the intersecting cell into R9 (Where the formula is kept). In this instance, it should find 1 at I3 and the 4 at C15 and return NN to box R9... am I wrong?

Thanks for the assistance.

pinmaster · 12-31-2006, 05:37 PM

Hi,

Try:

=OFFSET(C3,MATCH(R13,C4:B15,0),MATCH(S13,D3:O3))

or

=INDEX(C3:O15,MATCH(S13,C3:C15,0),MATCH(R13,C3:O3,0))

HTH
Jean-Guy

**VBA Noob** · 12-31-2006, 05:50 PM

Hi both,

As kingsolo is not using unqiue rand numbers then he's not always going to return NN

VBA Noob

**kingsolo** · 12-31-2006, 06:31 PM

So, you're saying that this won't work, or I'm using the wrong formula? The numbers in the red columns change by game. As does the location of the information in the white table. So, I want excel to find the number in the row/column then return the intersection of the two. The numbers that it is looking for (R11-14 & S11-14) are all input via a validation drop list. The person using the sheet has to input this data to get the sheet to operate correctly.

**VBA Noob** · 12-31-2006, 06:39 PM

It will work but may not return NN if the lookup finds another match e.g if

This assumes the random numbers aren't unqiue as per the version I downloaded e.g if another number 1 in T1Q2 then it will lookup that value first

VBA Noob

pinmaster · 12-31-2006, 06:53 PM

Hi,

If I am correct then T1Q2, T2Q2 ect... will have numbers from 0 to 9, representing the last digit of each football teams scores at the end of each quarter.

here's a revised version of the INDEX formula:

=INDEX(F6:O15,MATCH(S13,T2Q2,0),MATCH(R13,T1Q2,0))

Regards
Jean-Guy

**kingsolo** · 12-31-2006, 06:58 PM

Yeah, I see what I was doing wrong now! Thanks! Do you always have to go column first when you are doing a match, or can you have the row first... not that it really matters other than my personal knowledge bank.

Thanks for the help guys!

pinmaster · 12-31-2006, 07:05 PM

Hi,

Yes, always column first (to find the corresponding row num) and then row (to find the corresponding column number). Try this: select the cell with the formula then click on the "=" sign left of the formula bar to see the formula's parameters.

Cheers
Jean-Guy

**VBA Noob** · 12-31-2006, 07:07 PM

You might find this link helpful

http://www.contextures.com/xlFunctio...tml#IndexMatch

VBA Noob

**kingsolo** · 12-31-2006, 07:11 PM

Thank you guys for your input. I stared at that formula parameter thing thill I thought my eyes were going to blow up last night, and didn't get that... oh well, like I said before, sometimes I feel like an idiot!

Thanks again!