Find a number in a column then assign another cell number for the output number

Hi,
I am new to Excel and formulas.

I need to find a number (lets say #1) in a column of 10 numbers and when found assign a different cell's number as the output.

How? And what formula do I use to accomplish this?

Thank You

Hi
I'm not quite clear what you mean. Do you mean that if you find the value 1 in say A10, you want to substitute the value of 1 in A10 with a number from another cell, say E1?

If so you can't use a formula to assign a value to another cell.

You could highlight the whole of say column A:A, do a find for the value 1, and a replace with the formula =E1. This will replace the value 1 on A10 with the value in E1.

Is that something that works for you?

Rgds

Example:

I would like the final output value to be in column O Row 6.

I would like to search column R (where there are values of 1 to 9)
When the value "1" is found in column "R" lets say (in R21 we find the value 1)
I would like the output value to = the value in A21

In column R there are values in R3-R12-R21-R30-R39-R48-R57-R66 AND R75
All other cells in column R are blank.

In column A there are values in A3-A12-A21-A30-A39-A48-A57-A66-AND A75
All other cells in column A are blank.

I hope this is clear from a newbe.

Thanks again:

Hi,

Try

Please Login or Register  to view this content.

HTH

Sorry but,

=INDEX(R:R,MATCH(1,A:A),1) returns the value in R3 not the value in A21

Hi,

Sorry, got that reversed. It should have been

Please Login or Register  to view this content.

HU-RAYYYYY that works!

One last question: Could you explain the formula to me in layman's terms so that I can make adjustments?

THANKS

The Match() function finds the position of the value 1 in the range defined, which because it's the whole of column R equates to the row number.

The Index uses the row number that is the result of the MATCH() and looks in column A for that row number. The Index requires both a row & column index number, so since we're looking in column A the column index is 1.

Rgds