Further variation of Index/Match

[Glaswegian]

Hi

I received help and an excellent formula recently
http://www.vbaexpress.com/forum/showthread.php?t=35730

but I'm looking at a possible re-design of the workbook in question and wondered if it was possible to create a formula based on the following changes.

The dropdowns are still in F16 - F53.
The possible text values are now in a named range on another sheet - the sheet is called 'Tables' and the range Name is 'Answers'.
At the moment, the text answers still matches the row of the dropdown - however, some of the text is duplicated, so that might change.

Thanks .

[mdmackillop]

Can you post a sample?

[Bob Phillips]

Try


=INDEX(INDEX(Answers,ROW(F16)-ROW($F$15)+1,0),1,MATCH($F16,INDEX(Answers,1,0),0))

[Glaswegian]

Hi

Thanks for your replies.

xld - your formula produces an #N/A error.

I've attached a basic sample of the 'as is' position. I would like to do some redesign, such as moving the answers section to another sheet. The duplicated answers are a nuisance as well but I can live with that.

Thanks for any help.

[Bob Phillips]

If you data started in F16 as you said, if you have a named range called ANswers, it works fine.

[Glaswegian]

xld - my apologies.

I had inserted 2 new rows near the top and forgot to adjust your formula. It does indeed work perfectly - many thanks!