I want if 2 cells match to bring 1 cell back

Microsoft Excel 2003 (full product)
March 16, 2010 at 02:39:06
Specs: Windows Vista
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


See More: I want if 2 cells match to bring 1 cell back

Report •


#1
March 16, 2010 at 05:53:05
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 G1

Note 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.


Report •

#2
March 16, 2010 at 11:25:19
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


Report •

#3
March 16, 2010 at 12:40:20
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 two lookup functions, one to lookup Martindale to Saddleridge and one to lookup Pho (castleridge) to Casteleridge Area

If 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.00

Create 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 I1

In 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


Report •

Related Solutions

#4
March 16, 2010 at 13:49:08
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!!!


Report •

#5
March 16, 2010 at 14:35:35
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 =A1

If you make the formula in B1

=A$1
then 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


Report •

#6
March 16, 2010 at 15:01:02
Wow, amazing!!!! Thank you so much.

That would of saved me alot of time last year hahaha

Thanks a whole bunch


Report •

#7
March 16, 2010 at 19:20:52
You're very welcome.

Regards

Humar


Report •

Ask Question