is there any way to have a formula

[smiley0q0]

is there any way to have a formula that looks at a table and finds a specific number horizontally(column header), then looks for a specific number vertically, in a date field and finds where they intersect. here is an example...

Date code 21 22 25 26
6/1/01 A 1,000 1,500 1,250 1,750
6/1/01 C 2,000 2,500 2,250 2,750
6/1/01 D 3,000 3,500 3,250 3,750
6/2/01 A 4,000 4,500 4,250 4,750
6/2/01 B 5,000 5,500 5,250 5,750
6/2/01 C 6,000 6,500 6,250 6,750
6/2/01 D 7,000 7,500 7,250 7,750

i want to find code "A" of 6/1/01 column header "22", but sometimes there will be a 23 and/or a 24, so they will not always be the same column #. Is there a formula that i don't have to put in the number of columns over, like the vlookup? I don't have a problem finding the right row, it's just the columns are giving me some grief, because they aren't going to be the same every time.

Any suggestions or help would be greatly appreciated.

Thanks,
Smiley

 

[JVFriederick]

If you move your example data set to begin at A1, then paste the following equation into your spreadsheet :

=SUM((A2:A8=DATEVALUE("6/2/2001&quot)*(B2:B8="A&quot*(C1:F1=22)*(C2:F8))

Now press F2 key and then Ctrl+Enter to enter equation as an array formula. You should now have :

{=SUM((A2:A8=DATEVALUE("6/2/2001&quot)*(B2:B8="A&quot*(C1:F1=22)*(C2:F8))}

I would suggest placing your variables outside of the formula so you can more easily change them.

=SUM((A2:A8=QDate))*(B2:B8=QCode)*(C1:F1=QColumn)*(C2:F8))

A more detailed explaination can be found under FAQ for writing an equation in Excel.

 

[ETID]

Use the match function in place of your column ref. in your lookup formula

=VLOOKUP("your row lookup value",A2:F8,MATCH("your column lookup value,A1:F1))

 

[smiley0q0]

JV,

Thank you very much!!! I was using that same formula, but instead of
{=SUM((A2:A8=DATEVALUE("6/2/2001&quot)*(B2:B8="A&quot*(C1:F1=22)*(C2:F8))}

i was doing...
{=SUM((A2:A8=DATEVALUE("6/2/2001&quot)*(B2:B8="A&quot*(C2:C8))}
i didn't know you could do that, but hey now i do!!!
Where did you find out about this type of formula? I have the FAQ, is there anywhere else i can get some more info on this, like specifically how and why it works the way it does?
Thank you again!!
Smiley

 

[JVFriederick]

I saw an example of an array formula on a web site. At the time I was looking for some way to do matrix math like the old Fortran programs used to.

How and why it works? I don't have the slightest idea! (I don't know how a TV set really works either.......)

The little trick with the (C1:F1=22) is VERY handy when the column data is variable, such as in your example. I found the array formula very flexible and powerful after I got past the concept.

Glad this helped.

 

[smiley0q0]

Well hey it works wonders. Thank you VERY much.

C ya...

Smiley