INDEX/LOOKUP solution

**johnmitch38** · 10-26-2011, 06:36 AM

All,

On the attached, on "Summary" tab, I want to return the value for the corresponding name for the headings supplied (entitlement, remaining,sick)

The "Summary" tab should draw this info from the "Coleshill" or "Stratford" tab.

I know I could simply equals the cell for the relevant name but I want to be able to add new names in and use the same formula.

i.e. I want excel to look at the name on both sheets and return the relevant value for the three headings.

Hope I'm making myself clear.

Thanks.

**quekbc** · 10-26-2011, 08:07 AM

Hi johnmitch38,

Please see attached. I think it is what you are looking for.

**johnmitch38** · 10-26-2011, 08:36 AM

Hi quekbc, many thanks for the reply.

Yes that's returning the correct result by the looks of things. However, i don't understand the formula.

What is the relevance of the "R" numbers? Sorry to be a pain as youve been a real help but i like to get my head round why something works.

**quekbc** · 10-26-2011, 08:42 AM

No worries.

I used R1C1 reference style for the INDIRECT function.

This is indicated by a FALSE in the last parameter in the INDIRECT function. Saying R4 in R1C1 is like saying row 4, or 4:4 in A1 style. (I like to work in R1C1)

You can change the reference style by doing...
1) Change D3, E3 and F3 to 7:7, 9:9, and 5:5 respectively (make sure they are text)
2) and change the formula from
=IFERROR(INDEX(INDIRECT($C4&D$3,FALSE),MATCH($A4,INDIRECT($C4&"R4",FALSE),0)),0)
to
=IFERROR(INDEX(INDIRECT($C4&D$3),MATCH($A4,INDIRECT($C4&"4:4"),0)),0)

**johnmitch38** · 10-26-2011, 11:01 AM

Think i've finally got it now thanks.

I deleted a couple of rows and was struggling to figure out which reference i needed to changed.

Thanks again.