index..match - issue

[step_one]

Hi, having some issue with my formula of index(match.

I have attached a sample workbook. I'm trying to obtain the weekly sales or aka. in our company, nar , numbers for a few people. Here is my formula:

=INDEX('P1'!1:65536,MATCH($C4,INDEX('P1'!1:65536,,3),0),MATCH($E$2,INDEX('P1'!1:65536,,,1),0),MATCH(RIGHT(G$3,2),INDEX('P1'!1:65536,,,),0))

It's showing me a N/A. Now I'm sure I'm messing up defining the appropriate column reference but again, if someone can look at my workbook, i have highlighted in yellow the cell. Much appreciated.

Cheers

[MarvinP]

Hi Step_one,

Try using a VLookup formula instead of Index Match. Your table on Sheet P1 is not good in that it has merged cells for column heads and the same column names for many columns. This will keep you from finding what you need. The VLookup formula in the attached only needs the persons name and then count the number of columns over. It doesn't rely on duplicate column names.

Hope this helps

[jw01]

Hi Marvin. Jeez, I totally missed on a simple vlookup; got too caught up! Thanks for pointing out the obvious, thanks!

[step_one]

I have the same workbook, but im trying to reference another worksheet (rather it will be another workbook on my server but for the purpose of this I have saved it in my workbook).

Basically Im trying to obtain the revenue again for an individual. I have highlighted in yellow, the N/A Im getting from my index(match formula.


I'm trying to obtain the revenue #s from worksheet "New Auto & Purge Revenue" and for this example, it's row 119. I need to show the revenue for W1, W2, W3, W4 (which is row 6 on that worksheet).

here is my formula and attached is my workbook.

=INDEX('New Auto & Purge Revenue'!$B$5:$H$222,MATCH($C7,INDEX('New Auto & Purge Revenue'!$B$5:$H$222,,1),0),MATCH(RIGHT(H6,2),INDEX('New Auto & Purge Revenue'!$B$5:$H$222,,,1),0))


any thoughts, thank you.

[MarvinP]

Did you try the VLookup function I suggested?

[step_one]

Hello Marvin

Yes, but this is for another worksheet and it might require offset so i tried the index match.

for instance, in my data worksheet "New Auto & Purge Revenue"
the person's name is on row 115
the data i need for i.e. W1 is on row 119, column C, W2 row 119 column D ...until W4.

unless there is a vlookup(match? kindly advise, much thanks!

[NBVC]

The right 2 characters of P1W1 don't match anything on the other sheet (i.e. W1 doesn't match Wk 1)... Also you don't really need the inner INDEXES and I used ROWS($A$!:$A1) to offset the rows by 1 after find the C7 name...

Try:

=INDEX('New Auto & Purge Revenue'!$B$5:$H$222,MATCH($C7,'New Auto & Purge Revenue'!$B$5:$B$222,0)+ROWS($A$1:$A1),MATCH(REPLACE(RIGHT(H6,2),2,0,"K "),'New Auto & Purge Revenue'!$B$6:$H$6,0))

[step_one]

Brilliant, thank you for saving the day once again.

Final question, I had to change up the row reference as I needed the 4th figure from the name or in this case row 119

+ROWS($A1:$A$4)

thank you for your kind help.

CHeers

[NBVC]

If you are only looking for Actual Auto ($) Total only, then just hard-code an offset of 4 rows in the first MATCH():

=INDEX('New Auto & Purge Revenue'!$B$5:$H$222,MATCH($C7,'New Auto & Purge Revenue'!$B$5:$B$222,0)+4,MATCH(REPLACE(RIGHT(H6,2),2,0,"K "),'New Auto & Purge Revenue'!$B$6:$H$6,0))

[step_one]

NBVC

thx you for your prompt responses, your superb. Have a great weekend!

[step_one]

Hi there

Ive tried to incorporate the indirect.ext so I dont have to keep changing the "city" name however not sure if im placing something correctly or is my technique not correct?

indirect.ext:
=INDEX(INDIRECT.EXT("'[C_"&D159&"_P1W5_11.xls]New Auto & Purge Revenue'!$B$5:$H$222",false),MATCH(INDIRECT.EXT($B158,"'[C_"&D159&"_P1W5_11.xls]New Auto & Purge Revenue'!$B$5:$B$222",false),0)+ROWS($A$1:$A$4),MATCH(INDIRECT.EXT((REPLACE(RIGHT(I$6,2),2,0,"K "),"'[C_"&D159&"_P1W5_11.xls]New Auto & Purge Revenue'!$B$6:$H$6",false),0)))

original formula with city name i.e. concord (replacing concord with D159
=INDEX('[C_Concord_P1W5_11.xls]New Auto & Purge Revenue'!$B$5:$H$222,MATCH($B158,'[C_Concord_P1W5_11.xls]New Auto & Purge Revenue'!$B$5:$B$222,0)+ROWS($A$1:$A$4),MATCH(REPLACE(RIGHT(J$6,2),2,0,"K "),'[C_Concord_P1W5_11.xls]New Auto & Purge Revenue'!$B$6:$H$6,0))

It's giving me a formula error? your thoughts? cheers

[NBVC]

Try it like this:

=INDEX(INDIRECT.EXT("'[C_"&D159&"_P1W5_11.xls]New Auto & Purge Revenue'!$B$5:$H$222"),MATCH($B158,INDIRECT.EXT("'[C_"&D159&"_P1W5_11.xls]New Auto & Purge Revenue'!$B$5:$B$222"),0)+ROWS($A$1:$A$4),MATCH(REPLACE(RIGHT(J$6,2),2,0,"K "),INDIRECT.EXT("'[C_"&D159&"_P1W5_11.xls]New Auto & Purge Revenue'!$B$6:$H$6"),0))

[step_one]

NBVC

Thanks for your wonderful help.

It's a little strange that the indirect.ext formula above works when the path file is open. Otherwise it shows #REF error. Cant seem to get my head wrapped around that, even with the formula you have provided.

[NBVC]

You need to include the full path in the formula in order for it to work with a closed workbook.

e.g

=INDEX(INDIRECT.EXT("'C:\MyDocs\[C_"&D159&"_P1W5_11.xls]New Auto & Purge Revenue'!$B$5:$H$222"),MATCH($B158,INDIRECT.EXT("'C:\MyDocs\[C_"&D159&"_P1W5_11.xls]New Auto & Purge Revenue'!$B$5:$B$222"),0)+ROWS($A$1:$A$4),MATCH(REPLACE(RIGHT(J$6,2),2,0,"K "),INDIRECT.EXT("'C:\MyDocs\[C_"&D159&"_P1W5_11.xls]New Auto & Purge Revenue'!$B$6:$H$6"),0))

Replace C:\Mydocs\ with your actual full path to the document.

[jw01]

Sorry NBVC

Was unable to get back to you earlier as we had a power outage for a few hours. I realized when I was working with the formula it rquired the path. Again, brilliant help, your phenomenal.

Cheers mate.

[NBVC]

Are you sure this is your thread, jw01? I am confused as you responded twice as if it was your issue.