Use Excel to return a single value based on a range?

[falseadvertise]

I need to pull a value from one sheet and put it on another based on a range of 1-40.

In column F I have F4:F43 (Total 40).
In Column A I have A4:A43 (Total 40).

What i need to do is return the value from A to a separate sheet based on the value in F. This is made difficult because currently column F is listed in order from 1-40. I will need to sort column F regularly and need the updates to reflect the changes.

Ex. - I am going to sort largest to smallest or smallest to largest and still need the same value in column A that is associated with the similar number in column F. So column F might say '1' (F4) and column A will say 'TOP 1' (A4) then i sort largest to smallest and F4 now says '40' and A4 now says 'TOP 40'. Whichever way i sort, i need my cell on the next sheet (assume sheet2!H1) to say the word 'TOP 1' and i need sheet!2H40 to say 'TOP 40'.

I can get an if statement to work if i do not sort the columns but when i do sort them it looses its consistency.

An equation that works before a sort:
=IF(Sheet1!F4=1,Sheet1!A4,"")

The equation i tried but wont work:
=IF(Sheet1!F4:F43=1,Sheet1!A4,"")

the problem is the Sheet1!F4:F43

Any ideas??

 

[GlennUK]

Are the values in Sheet1!F4:F43 all unique ( i.e. no duplicates )?

 

[falseadvertise]

All values in both columns will be unique. They are all labeled 1-40. I will sort using a different column from A and F but the data in columns A and F NEED to match when they are transposed to the next sheet.

There will eventually be 4 sheets total.
1) Data entry sheet
2) Hidden sheet that will sort the data using an array. (This is the reason it is hidden so no one compromises the formulas)
3) Hidden sheet that sorts and compiles the data for a matrix
4) The final output sheet that is read only

The point is that the first sheet is really simple. Anyone who works here can edit it and see the results on the final sheet. In doing so they will not compromise the formulas and i do not have to explain to everyone how they work.

I am really close to having this complete. This is my last step. Basically a 'clean up'.

Any ideas will help.

 

[GlennUK]

Have a look at this and see if you can understand my approach:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1
2								Fetch results
3	Texts	Various	blah	blah	blah	MySort		MySort	Texts
4	TOP1	4				1		1	TOP1
5	TOP35	5				35		2	TOP2
6	TOP28	6				28		3	TOP3
7	TOP23	8				23		4	TOP4
8	TOP19	9				19		5	TOP5
9	TOP27	11				27		6	TOP6
Sheet31

 

[falseadvertise]

Glenn,

This works really well. Thank you.

One last thing. I was trying to nest this in an IF statement so that i can say return "TOP 1" if true and return "Check Data" if false. I am not vary familiar with nesting formulas so I'm not 100% sure where to put it.

I am going to keep working on that but if you have an idea let me know...

Thanks again!!

 

[falseadvertise]

In looking at this i actually don't see any chance of there being an error. I don't think an IF statement is even necessary.

 

[falseadvertise]

Is there a way i can do the same thing with non unique values. I am trying to copy the cells on one sheet to the cells on another. The problem now is that my cells from column E are a range from 1-5 and can repeat several times??

Maybe a vlookup?

 

[GlennUK]

Can you give an example of what you mean? I'm finding it hard to imagine what you are after.

 

[falseadvertise]

I have posted this a few times now... Thanks for looking.

In the old question all my values were unique so a MATCH function was appropriate. Now i have one set of strictly unique variables (column G) and one set of variables that can range from 0-5 (column E).

I think something like a vlookup may be more appropriate but i am not very familiar with them.


My Problem...

I need to pull a value from one sheet and put it on another based on a range of 1-40.

In column E I have E4:E43 (Total 40). All values between 0 and 5.
In Column G I have G4:G43 (Total 40). Values 1 - 40.

What i need to do is return the value from E to a separate sheet based on the value in G. The values in G are all unique (a number from 1-40) but the values on E are a range from 1-5 so i don't think a MATCH function is appropriate.

The reason this is important is because i will need to sort the values in the main sheet based high to low or low to high but the values will change regularly. When i sort the main sheet i need to make sure the second sheet stays constant. (this sheet feeds my matrix)

I might have several cells in my E column that are 3 but only one cell in my G column that is 3.

Ex.

E G
2 1
4 2
1 3
2 4
3 5
2 6
4 7

and so on...

When i sort column E from High to low, i need my second sheet to look exactly the same as before i sorted the main sheet. However, when i sort column E, column G is also going to rearrange itself. On the second sheet, the cells should stay constant. Column G is the base on both sheets.


I hope this was clear,

Any Ideas???

 

[GlennUK]

Yes, that's clear. The solution is exactly what I have already given.

Before sorting:

Excel Workbook
	E	F	G	H	I
2	Before sorting	Index list		Fetch list ( on a separate sheet, if you like )
3	Values 0-5	Values 1-40		Index values	Values 0-5
4	5	1		1	5
5	2	2		2	2
6	4	3		3	4
7	2	4		4	2
8	1	5		5	1
9	1	6		6	1
Sheet1

After sorting:

Excel Workbook
	E	F	G	H	I
2	Before sorting	Index list		Fetch list ( on a separate sheet, if you like )
3	Values 0-5	Values 1-40		Index values	Values 0-5
4	0	8		1	5
5	0	9		2	2
6	0	10		3	4
7	0	11		4	2
8	0	27		5	1
9	0	34		6	1
Sheet1