Hi , I have this issue and would greatly appreciate a resolution. Thanks!

Ok. heres what i want. heres a diagram

a b c d e

1 5.00 5.00 5.00 5.00 5.00

2 5.00 5.00 5.00 5.00 5.00

3 4.50 4.50 4.00 4.00 5.00

4 4.00 4.00 4.50 4.50 4.50

5 4.50 4.50 4.00 4.00 5.00

So if on my drop down menu I go 1 and a are matching i want it to bring up the correct price in it.not sure if this is clear enough

Thanks

Hi, Assuming that your table of values is in cells A1 to E5,

And there are drop-downs in cells G1 and H1

G1 selects the row (1 to 5) and

H1 selects the column (A to E)do this:

In cells M1 to M5 create a list of column letters A to E which are used for the drop-down in H1

In cells N1 to N5 create a list of row numbers 1 to 5 which are used for the drop-down in G1Note that the number drop-down must be in the column to the right of the column with the letters

The result appears in cell I1

In cell I1 enter this formula:=OFFSET(A1,G1-1,VLOOKUP(H1,M1:N5,2, FALSE)-1)This formula uses the OFFSET(row, column) function.

The row offset is straightforward, just -1 required as the first row has an offset of zero, not 1

To get the column offset, the VLOOKUP() function is used to return an offset number corresponding to the letter.Hope this was what you were looking for.

I notice that you only talk about one drop down - if that really is the case and it contains all 25 options in one list, please let us know.

Regards

PS If posting tabulated data again, put it between <pre> and </pre> tags that you can find above the reply box. The data will line up better.

Hi, thanks for this answer.. although it does seem to work with the "letters" and "numbers " but does not seem to work with this kind of data. could you advice on why please?

Martindale Taradale Castleridge Falconridge Saddleridge Pho (castleridge) 5 5 5 5 5 2 for 1 Pizza 5 5 5 5 5 My Donair 4.5 4.5 4 4 5 Legends 4 4 4.5 4.5 4.5 Castleridge Area 4.5 4.5 4 4 5

Hi, I didn't work because what you just posted is different to what you asked in your original post. Using a number as an offset did not require a lookup table.

For what you posted this time, there have to be

twolookup functions, one to lookup Martindale to Saddleridge and one to lookup Pho (castleridge) to Casteleridge AreaIf the table with headings that you posted is in cells A1 to F6 as follows, (I have abbreviated some of the names to better fit into this post):

A B C D E F 1 M'dale T'dale C'ridge F'ridge S'ridge 2 Pho (castleridge) 5.00 5.00 5.00 5.00 5.00 3 2 for 1 Pizza 5.00 5.00 5.00 5.00 5.00 4 My Donair 4.50 4.50 4.00 4.00 5.00 5 Legends 4.00 4.00 4.50 4.50 4.50 6 Castleridge Area 4.50 4.50 4.00 4.00 5.00Create two lookup tables in columns M/N and O/P as follows:

M N O P 1 Pho (castleridge) 1 Martindale 1 2 2 for 1 Pizza 2 Taradale 2 3 My Donair 3 Castleridge 3 4 Legends 4 Falconridge 4 5 Castleridge Area 5 Saddleridge 5

Use the list in column M to make the validation list for a drop-down in cell H1 and

use the list in column O to make the validation list for a drop-down in cell I1In cell J1 enter this formula:

=OFFSET(A1,VLOOKUP(H1,M1:N5,2,FALSE),VLOOKUP(I1,O1:P5,2,FALSE))The formula uses the OFFSET(row,column) function as before, but this time, both row and column offsets are found by using VLOOKUP() functions.

Regards

Yeah that works really. Thank you so much. With that note im just wondering on how to do this on a full page so the formula keeps looking at the correct information over and over again so that The row J always collects the right information and the formula doesnt change.

Im not sure if that is clear.. but when I try to copy the formula over more than one row .. it does not work on the rows afterwards.

Your hep is greatly appreciated again!!!

Hi, You may have noticed that some formulas posted have $ signs in them.

The part of the formula that has an $ before it will not change when dragged.

For example in cell B1 put this formula

=$A$1

Drag the formula down a row and it is still =A1, or drag it right, and it is still =A1If you make the formula in B1

=A$1then drag it down a row and it is still =A$1, but drag it one column to the right and it becomes =B$1.In your formula decide what needs to stay static, typically the lookup tables, and add $ signs to them, something like this:

=OFFSET(A1,VLOOKUP(H1,$M$1:$N$5,2,FALSE),VLOOKUP(I1,$O$1:$P$5,2,FALSE))A quick way to add $ signs is to select the part of the formula to keep static, say M1:N5, then use f4.

When you click the f4 function key it becomes $M$1:$N$5, click f4 again and you get M$1:N$5 then $M1:$N5 and finally back to M1:N5.

I don't know exactly what parts of your formulas need to stay put, but hopefully the above information will enable you to lock the right parts, so that dragging works correctly.

Regards

Wow, amazing!!!! Thank you so much. That would of saved me alot of time last year hahaha

Thanks a whole bunch

You're very welcome. Regards

Humar

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History