This should be easy. Find a value in a matrix in Excel -Update: It's hasn't been easy

Satsumomo

Here's the problem:



Basically, I will type "1951" and then "Feb" in separate cells, and right below them I would like to give me the value for 1951 Feb based on the top most and leftmost column/rows. In the image, the gray cell 0.012 is what I should get when applying the function.

So yes, something that searches row/column, and gives me the "intersection" value.

I know I've done this before, but I just simply cannot remember what function I used to solve this. I thought about OFFSET but I'm not getting anywhere and it seems way more complicated than it really should.

Dunadan019

=HLOOKUP("feb",A1:F8,H2-A2,False)

Satsumomo

Huh? By just looking at that I don't think it does what I need at all.

soxbox

I don't have excel in front of me ATM, but there's a thing you can toggle to make the spreadsheet switch from using A1/C5 type references to using ROW and COL references - it's much easier to reference a row from a formula result using that style.

Then the HLOOKUP function will do what you want I think - you just need to adjust the row reference by year math.

LaOs

I thought this probably would have been fixed up by the time I got back to work today, but I see it hasn't really. Anyway, here's a formula that will work just fine using HLOOKUP:

=IF(OR($H$2="",$I$2="",),"",IF(ISNA(HLOOKUP($I$2,$A$1:$F$8,MATCH($H$2,$A$1:$A$8,0),FALSE)),"n/a",HLOOKUP($I$2,$A$1:$F$8,MATCH($H$2,$A$1:$A$8,0),FALSE)))

What it does, assuming your table starts at A1 and your input cells are relative to the table as shown in your image, is checks first to make sure that you have something entered in both cells (if either are blank, the output cell will remain blank). Then, it checks to see if the HLOOKUP will result in an error--if it does, it will output "n/a" instead of the error. If there's no error, it does the HLOOKUP, looking first for the right value along the headers of the table and then uses MATCH to make sure it finds the right year value along the left.

There's a bit in there that's not strictly necessary for you, so far as I know, but it's just my habit to output "clean" results whether there's a problem or not. I don't like seeing error cells. :P Let me know if you have any questions.

Tag

This is easy if you convert your data to a pivot table with years in the column, months across the top, and values as the sum (such that it looks nearly identical to your current format.

Then if your month and year are stored in J1 and K1 respectively, and the upper left corner of the pivot table is in A3, your cell should be simply:

=GETPIVOTDATA("Value", $A$3, "Month", $J$1, "Year", $K$1)
or if your table is on a different sheet than the cell pulling it
=GETPIVOTDATA("Value",'Pivot Table Sheet'!$A$3, "Month", $J$1, "Year", $K$1)

If there is an error it will spit out a #REF error so you can tell too. Converting to a Pivot Table is slightly annoying but not too bad if you have used them before and the GetPivotData function is ludicrously powerful with more complex data sets.

Edit:

Alternatively, you can hack it together without a pivot table:

If your table corner is in A1, month in J1, year in K1
=VLOOKUP(K1,A1:F8,MATCH(J1,A1:F1),FALSE)

Match() provides the relative position inside of an array of a given value. Since in this case the relative position is the column number, it works as the column index in a vlookup.

Satsumomo

Convert to a Pivot table! That's the step I was missing! I was sure I had been able to do this before, but it was so long ago I couldn't remember.

Thank you very much for all the help guys, I learned new tricks in here as well.

Satsumomo

Haw haw I'm stuck, the pivot table isn't being nice to me.

This is what it looks like:



I have my sheet set up in such a way that I will type 08/08/2008 and in the "Month" cell I will have "ago" show up and year will say "2008".

This conflicts with the pivot table having each column calles "Suma de Ago" instead of just "ago". I manually modified the cell to say "Suma de Ago" and I still get a #REF!# or #VALUE!# error.

So I then went and did the other HLOOKUP and VLOOKUP "tricks" posted here and they're not giving me results either.

For Laos's method, I only get "n/a" specified in the formula, it's always giving me that result, and for Tag's, I'm getting a #REF!# error.

Halp.

Tag

Hmm your columns should not be sums necessarily. In your pivot table field list you should have year as the row label, month as the column label, and the values in the ∑Values box (Note: I use Excel 2007 so yours may be slightly different). Try arranging it like that.

Alternatively, how are you extracting the month/year for your cells? It is possible excel is being very picky about formatted numbering and not matching "February" the date with "February" the text string. Try entering in a couple values to your cells manually and see if it works. (I know you entered "Suma de Ago" manually but I think the "Suma de" might actually mess it up in that case.)

Edit: Here is a quick mock up composite I made which works with manually typed month/year.
The data that makes up the pivot table is pasted in to keep it one image.

Edit2: Ah I tried making a Pivot with your starting format and it makes the pivot table look like yours by default. I'll play around with it to see if there's an easy modification but you can also try reformatting the data to look like mine and making a new pivot table.

Edit3: You could also try my Vlookup method with your original data format.
=VLOOKUP(K1,A1:F8,MATCH(J1,A1:F1,0),FALSE)

You may have to change the bit in yellow to MATCH(J1,A1:F1,0)-X where X is some number to get it lined up with wherever your table is on the sheet.

Also NOTE, this is slightly different than before because I forgot to include the 0 at the end of the Match function in my first post. The 0 makes it an exact match.

LaOs

Satsumomo wrote: »

For Laos's method, I only get "n/a" specified in the formula, it's always giving me that result, and for Tag's, I'm getting a #REF!# error.

Halp.

Do you get a text "n/a" or the N/A error output? If you can't tell, change the "n/a" in the middle of my formula to something like "x" so you can easily tell what's happening.

Tag

Ahha, found the issue with your Pivot Table Satsumomo. With your existing pivot table, just use:

=GETPIVOTDATA(H3 & "",$A$6,"Year",I3)

Where H3 is your cell with the month, I3 is your cell with the year, and $A$6 is any cell within your pivot table (generally use the upper left corner, but any will work).

You need that & "" to make H3 a string for excel, weird quirk that was throwing me off.

Satsumomo

LaOs wrote: »

Satsumomo wrote: »

For Laos's method, I only get "n/a" specified in the formula, it's always giving me that result, and for Tag's, I'm getting a #REF!# error.

Halp.

Do you get a text "n/a" or the N/A error output? If you can't tell, change the "n/a" in the middle of my formula to something like "x" so you can easily tell what's happening.

I'm getting the text "n/a".

Tag, I tried your last post, and it gave me a #REF error.

I'm currently going to change the data layout to make it do the pivot table correctly, I'm using Office 2007, and with my current data, it's not letting me make the pivot table any other way. Argh!

Edit:

Ooh! Your last post works if instead of putting just "sep" I actually put "Suma de sep". So basically I need to either make the pivot table display months (It wont let me, telling me that it's already being used) or format my sheet to output "sep" as "Suma de sep" instead.

Now my problem is that having 09/09/2009 output to only month will always show "september" in lower case. The Pivot table's headers always have capitalization on the first letter. Of course, Excel is being case sensitive here, and giving me a #REF!#. Argh!

Edit 2:

Argh, ok so when asking to import Pivot data, it doesn't like reading dates. I think this has been the main problem since the start. Import/Vlookup/Hlookup have all been searching for a text string, but in reality the cell is a date. I suppose the action to be done here is to make that cell output a text string?

Edit 3:

Using =text( I've been able to make the date be text. However, it's always lower-case, and as I said earlier, the pivot table has proper capitalization, which autocorrects itself. However, I made it show months as numbers, changed months to numbers in the Pivot table, and... no dice. Why is this so complicated!

Edit 4:

SUCCESS!!

I forgot to change year to text as well. Did that and POW! It's working!

LaOs

I figured that it might have been an issue with your data in the table was entered as numbers or whatever it auto-decided your data was, where the formulas are looking for text entries. Glad you've been able to make it work, though.

[Edit]
I haven't really ever used pivot tables, but I hear they are quite good at making this sort of thing easy, so it's probably a good idea to get good and comfortable with them. Kudos.