Excel LOOKUP question...

Joebob · 4 May 2010 at 21:56

I have a table of data with multiple columns and rows. For sake of argument I will call the Rows (X) and the Columns (Y).

If you look at the image above. I want to be able to enter the values in cells C1 & C2 (I'm actually going to use a drop down list for this) and use Lookup to return the appropriate value to cell C3 - in the example it would be D11 (£7.50).

How would I do this?

Joebob · 4 May 2010 at 22:29

Excellent - cheers for the help. Example uploaded here

I will have a go at applying any suggested formulas etc, but if I get stuck then would it be possible to upload the actual document I'm working on for an applied fix?

Sorry to be cheeky but where needs must etc.

Joebob · 5 May 2010 at 09:48

Ok, I have very nearly got it working the way I want just using Vlookup and Hlookup. Some of the values are returning incorrect values though.

http://spreadsheets.google.com/ccc?key=0AmDd843xYlOOdHUzcENmNS1RVmhwUUxnRHVjTFdXbEE&hl=en_GB

Link to doc is above if anyone can help I will be eternally gratefull.

Cheers.

Joebob · 6 May 2010 at 09:45

Excellent - thanks for the replies. I have the actual spreadsheet working just using vlookup and hlookup - it wasn't working with alpha values so I used numerical values instead and it works fine... will post the spreadsheet again when I get home from work as i would really like it to work with the alpha values instead.

I need to look into this OFFSET function more I think.

Joebob · 6 May 2010 at 16:53

I have used the formulas you have provided and they work great - thanks.

I have taken a deep long look at the formula and I'm fairly sure I understand how it works although I have been unable to emulate it.

http://spreadsheets.google.com/ccc?key=0AmDd843xYlOOdHpWVDFGd1huU2x1d1hjNUw2NXJRWUE&hl=en_GB

I need the formula in D3 to work correctly and can't see what I'm doing wrong.???

Also I need Cells D5 & D6 to return correct values but they only work when some of the Value types (cell B3) are selected but not others!!!

HELP!!!

Joebob · 6 May 2010 at 19:45

I have sorted D5 & D6 using "if and "hlookup" - D3 is really beginning to bug me though...!!!

Can anyone explain how OFFSET & MATCH actually work - I really need to get my head around it to see why it isn't working for me. I got it to return results but they were wrong... similar to the initial problem I had before switching from alpha to numeric values!

I have uploaded the actual spreadsheet here: http://www.josephlock.co.uk/misc/comm_calc.xls