# INDEX/LOOKUP solution

1. ## INDEX/LOOKUP solution

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.

2. ## Re: INDEX/LOOKUP solution

Hi johnmitch38,

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

3. ## Re: INDEX/LOOKUP solution

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.

4. ## Re: INDEX/LOOKUP solution

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)

5. ## Re: INDEX/LOOKUP solution

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1