index match return value

[almostgenius]

hi,
just a quick question, can an index match return the value that it looks up to a different cell than the one it is in?

[TMS]

Quick answer: no.

Regards, TMS

[Pete_UK]

Yes, you can add or subtract values in the MATCH part of the formula, so that you can select different rows or columns.

Hope this helps.

Pete

[almostgenius]

Yes, you can add or subtract values in the MATCH part of the formula, so that you can select different rows or columns

yeah just knowing it can be done helps.
here is a formula i have and it returns the value to the cell it is in, can you help me with the syntax so it returns the value to a different cell? any cell will do i can change it to suit.
=INDEX(Index!$E$2:$AP$300, MATCH(A$2, Index!$A$2:$A$300, 0), ROW($A1))

many thanks in advance

[Glenn Kennedy]

I think that your request may have ben misunderstood.... You can't (sa far as I'm aware) tell Excel to put ht evalue elsewhere. you can copy the value to another cell, or put the formula to the palce where you want the answer to be. I also think that if you were to explain better (with a sample sheet) what you want (and why) it might be possible to come up with an alternative.

[Pete_UK]

I agree with Glenn - a formula can't send a value to another cell, it can only display a value in the cell in which the formula is placed. I misunderstood what you were asking.

Pete

[TMS]

For clarification, a longer answer: a formula, INDEX/MATCH or whatever, can only return a value to the cell where the formula resides. It cannot output a value to a different cell. The value returned by the MATCH can be adjusted by a constant value or another formula but, again, the result is in the cell with the formula in it.

What you are asking can not be done.

Regards, TMS

[Jacc]

It can if you put it in a macro.

[TMS]

@Jacc:

It can if you put it in a macro.

Yes, it probably can, but that wasn't the question and the question is in the Formulas and Functions sub-forum, not VBA.


Perhaps you would care to provide an example of a macro or event handler that would cater for the OP's requirements as quoted in Post #4?

[almostgenius]

gosh thanks to everyone for putting in so much effort for just a simple thing like this. I mean honestly i don't do this for work or someone else, its just for fun to use in a game that i play with mates on the weekends so i really appreciate how in depth you are all getting for little old me.

in regard to a sample sheet, i would love to post one but the data makes the file size too large and i don't know how to break it apart and still make it work but to explain further for those who are interested:
i have a sheet with cascading drop downs and a reset button that deletes the drop downs. i put the above formula into the cels beneath the drop downs to reference information relevant to what i pick in the drop downs which works well but then i realised the reset button clears the drop downs but not the information underneath. so anyway i extended the range on the reset to cover the extra cells in the columns but didn't realise it also deletes the formulas in those cells as well. that is why i thought i might be able to still use the formula but put it in another cell and tell it to drop the indexed value elsewhere that i want.

thanks for the help anyway

[TMS]

If you're already using VBA to clear cells, then just add another line of code to put the formula back.

For example:

Please Login or Register  to view this content.

Change "X30" to the cell where you want the formula to go (where it was originally)


Regards, TMS

[Jacc]

@Jacc

Perhaps you would care to provide an example of a macro or event handler that would cater for the OP's requirements as quoted in Post #4?

Not anymore, no.

[TMS]

@Jacc: oh, I'm disappointed

[Jacc]

Yup, that's a big loss for the forum. It would have been a great event handler.

[TMS]

@Jacc: I like you

[almostgenius]

If you're already using VBA to clear cells, then just add another line of code to put the formula back.

Change "X30" to the cell where you want the formula to go (where it was originally)

TMS,
thanks so very muchly for your awesome help i used your line in the vba i have adding it to the end of the reset function and then changed the "X30" as advised and it worked perfectly!! so thanks.

oh and i learned something odd, in the sheet i have, the formula goes into a 50 by 50 cell grid but not the top 2 rows as thats where i have some text and the cascading drop downs, so i was confounded briefly on how i would change the cell reference to put the formula back into all 50 rows and columns without it going into the top 2 rows.
first i tried the reference A3:A50 and that worked fine for the first row. then i just wildly guessed that if i put a comma after the A50 i could reference the second row like this A3:A50,B3:B50 and that worked too. but then i realised if i was going to do it this way, i would have to do it for all 50 columns and that would be just ridiculous so i guessed again wildly and thought what would happen if i just referenced the lot like this A3:AX50 thinking it would show an error for trying to put the formula in rows 1 and 2 but to my surprise, it only put the formula in to rows 3 onwards. so it worked perfectly.

your help has been soo awesome i thank you so very much

[TMS]

You're welcome.

[almostgenius]

@TMS i tried replying to your earlier quote with the code but it didn't seem to work, however your code was awesome and worked a treat.

thanks so much you've completely answered my question and then some, ill leave this thread for now and post my other questions (although related to this workbook I'm making) in new threads in the appropriate places.

cheers!

[TMS]

You're welcome. Thanks for the rep.

[Jacc]

@TMS Peace out!

*fist bump*