HELP! Microsoft Excel doing my head in!!

Anyone good with Excel?

I want to reference a table of values so that when I put the X value in one cell and the Y value in another, it looks up the table and returns the correct value.

I have found looking up an X value OR a Y value in the help, with HLOOKUP or VLOOKUP functions. But this only reads across OR down a table with a single set of values. I want to cross reference across AND down.

The table below is just an example of one I have already created from another purpose and is small enough to lookup values manually, but the table I want to look up hasn't been created yet and it will be much much bigger. The values in the table won't have any mathematical correlation to each other so I can't calculate the result with a formula, it has be a straight table lookup.

Anyone know how to do it in Excel?

no idea, may need to use a bit of VBA...

The Offset() function is the one you're looking for, combined with Match(). It's probably better to google for examples, but I'll still try here:

Lets assume I have a list of bikes down column A, with a header, so the data we're interested in is A2:A20. In the first row, we have a list of dates, ranging B1:Z1. In this table, we have the name of the rider, for any given bike, on any given date.

To find the rider of the SV650 on 17/01/09, you'd do the following:
=Offset(A1,Match("SV650",A1:A20,0)-1,Match("17/01/09",A1:Z1,0)-1)

NB, the Match()-1 is needed to correct the range values.

HTH.

[BoltonSte]

I'll ask my boss tomorrow, he's always doing this sort of stuff for us, it'll give him something to do as we're working 3-11 this week.

Edit - Never mind then,

[Mark_h]

http://www.mrexcel.com/

Fixes me every time and I have do do some very weird Excel stuff at times.

you need a vlookup formula with a hlookup formula nested within it to tell it how many columns to count across from the column with the x value in. you'll need a new row just under the one with the y value in, that just counts up, 1,2,3,4 etc. Its this row that you reference with the hlookup formula.

Well i know what i mean anyway - lol.

I'd do it for ya, but i'm on an old laptop with no excel. If you want to e-mail me the spreadsheet i'll do it as soon as i log on at work tomorrow. would only take a min. drop me pm if u want my e-mail address.

You can do it by conning excell!

Set up a "crib sheet" for the y axis so that it can work out which column the data it needs is in.

Y Axis...Column
0............2
0.5..... ...3
1............4
1.5.........5

Then use the column No it finds in the main vlookup command ->
=VLOOKUP(A2,$E$2:$I$13,(VLOOKUP(B2,K3:L9,2,FALSE)) ,FALSE)

If you PM me your e-mail I'll shoot over the sample spread sheet I worked out as I can't see how to attach anything to a PM

Tris

PS there are probably more sophisticated or faster solutions, but this will work

Thanks for the help guys. I've sure they all work, but I tried Baph's solution as it was the first one, and it works a treat, so especially thanks to Baph.