Excel LOOKUP question...

Joebob

Joebob

Joebob

Soldato
Joined
27 Sep 2005
Posts
5,909
Location
Burbage, Hinckley
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).

lookup.gif


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?
 
Last edited:
Make use of both OFFSET and MATCH

Match will tell you the nth row/column an item is in

Offset, well I assume you can figure that one out:D

=offset(A5,match(C1,B5:E5),match(C2,A6:A15))
Click to expand...

Thats essentially it, although please note I might have my rows/columns muddled, but you will find out when you try it.
 
Hi I'm too tired to experiment but the easiest way to do this would be to use the offset function I think.

OFFSET($A$5,c1,c2)

You would have to create a formula in c1 to change the name from high to a number.

So in your example this would count 6 rows down from A5 then assuming you transform high to 3 it would then move 3 columns across so the result would be
7.50.

You may also have to use indirect in place of the referent c1 and c2.

I'm too lazy to copy your data but if you post an example I can do it for you.

cheers
 
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. ;)
 
You need to have ,FALSE at the end of your lookup formulae. This ensures that only an exact match will be considered. Otherwise it will fnid what it perceives to be the closest match.
 
Or even better, here is the formula you want as per attached google document:-

=OFFSET(A10,MATCH(B4,A11:A60,0),MATCH(B3,B10:F10,0))
Click to expand...

I omitted the ,0 in my original match statement.

What its doing is offsetting the row in the first match, and then offsetting the column in the second.

Sorted.
 
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.
 
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!!!
 
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
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom