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:
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.
Please Login or Register to view this content.
Cheers
Last edited by NBVC; 04-29-2011 at 03:54 PM.
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
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Hi Marvin. Jeez, I totally missed on a simple vlookup; got too caught up! Thanks for pointing out the obvious, thanks!
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.
Did you try the VLookup function I suggested?
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!
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:
Please Login or Register to view this content.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
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
Last edited by step_one; 04-29-2011 at 02:24 PM.
If you are only looking for Actual Auto ($) Total only, then just hard-code an offset of 4 rows in the first MATCH():
Please Login or Register to view this content.
NBVC
thx you for your prompt responses, your superb. Have a great weekend!
Last edited by step_one; 04-29-2011 at 03:10 PM.
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
Try it like this:
Please Login or Register to view this content.
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.
You need to include the full path in the formula in order for it to work with a closed workbook.
e.g
Replace C:\Mydocs\ with your actual full path to the document.Please Login or Register to view this content.
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.
Are you sure this is your thread, jw01? I am confused as you responded twice as if it was your issue.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks