• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup values from pivot table

I can probably help, can you share that file using dropbox or something though? That speedy share site wants me to download some crappy download manager.
 
Hi ..
In B4 use formula as..

=INDEX('Look Data -Pivot Table'!$C$4:$K$46,MATCH(Result!$A4,OFFSET('Look Data -Pivot Table'!$A$4,MATCH($A$2,'Look Data -Pivot Table'!$A$5:$A$44,0)-1,1,18),0),MATCH("*"&Result!B$1&"*",'Look Data -Pivot Table'!$C$4:$K$4,0))

still you have to change red one when you move to next record..for "ILCH MICKOVSKI"
give some time.. will cover this one also.. :)
 
Hi Joseph ,

Try the following array formula , to be entered using CTRL SHIFT ENTER :

=SUM(IF(IFERROR(LOOKUP(ROW('Look Data -Pivot Table'!$A$1:$A$306),IF('Look Data -Pivot Table'!$A$1:$A$306<>"",ROW('Look Data -Pivot Table'!$A$1:$A$306))),0)=MATCH($A21,'Look Data -Pivot Table'!$A$1:$A$306,0),IF($A22='Look Data -Pivot Table'!$B$1:$B$306,OFFSET('Look Data -Pivot Table'!$C$1:$C$306,,COLUMN(B19)-COLUMN(Result!$B19)))))

You will need to change the items in BOLD when you copy it down.

Narayan
 
thanks narayan to get your help again. I am using 2003 version of excel and i guess the formula you provided is for 2010. I will soon update to 2010 , i think i am falling behind the advanced capabilites of Excel 2010.
I guess there wont be an answer for 2003
 
Hi Joseph ,

I have used the IFERROR function , which is available in Excel 2007 and later versions.

Since you have Excel 2003 , what you can do is use the ISERROR function ; however , the formula will become longer.

Suppose we have the following formula :

=A1/B1

When B1 is 0 , the above formula will give a #DIV/0! error.

We can revise our formula using an IF function to take care of the error as follows :

=IF(B1=0,0,A1/B1)

Another option is to use the IFERROR function as follows :

=IFERROR(A1/B1,0)

In case you do not have the IFERROR function in your version of Excel , you can use the ISERROR function as follows :

=IF(ISERROR(A1/B1),0,A1/B1)

What is clear is that the portion A1/B1 has to be repeated.

In your formula , the portion to be repeated is quite lengthy ; so repeating it will make the formula even longer.

Try the following array formula , to be entered using CTRL SHIFT ENTER :

=SUM(IF(IF(ISERROR(LOOKUP(ROW('Look Data -Pivot Table'!$A$1:$A$306),IF('Look Data -Pivot Table'!$A$1:$A$306<>"",ROW('Look Data -Pivot Table'!$A$1:$A$306)))),0,LOOKUP(ROW('Look Data -Pivot Table'!$A$1:$A$306),IF('Look Data -Pivot Table'!$A$1:$A$306<>"",ROW('Look Data -Pivot Table'!$A$1:$A$306))))=MATCH($A21,'Look Data -Pivot Table'!$A$1:$A$306,0),IF($A22='Look Data -Pivot Table'!$B$1:$B$306,OFFSET('Look Data -Pivot Table'!$C$1:$C$306,,COLUMN(B19)-COLUMN(Result!$B19)))))

You will need to change the items in BOLD when you copy it down.

Narayan
 
Hi Joseph..

Try this ..

In B4 use this Formula..

=IF(ISERROR(INDEX('Look Data -Pivot Table'!$C$4:$K$46,MATCH(Result!$A4,OFFSET('Look Data -Pivot Table'!$A$4,MATCH(LOOKUP(2,1/($A$2:$A3=""),$A$1:$A2),'Look Data -Pivot Table'!$A$4:$A$46,0)-2,1,18),0)+MATCH(LOOKUP(2,1/($A$2:$A3=""),$A$1:$A2),'Look Data -Pivot Table'!$A$4:$A$46,0)-2,MATCH("*"&Result!B$1&"*",'Look Data -Pivot Table'!$C$4:$K$4,0))),"",INDEX('Look Data -Pivot Table'!$C$4:$K$46,MATCH(Result!$A4,OFFSET('Look Data -Pivot Table'!$A$4,MATCH(LOOKUP(2,1/($A$2:$A3=""),$A$1:$A2),'Look Data -Pivot Table'!$A$4:$A$46,0)-2,1,18),0)+MATCH(LOOKUP(2,1/($A$2:$A3=""),$A$1:$A2),'Look Data -Pivot Table'!$A$4:$A$46,0)-2,MATCH("*"&Result!B$1&"*",'Look Data -Pivot Table'!$C$4:$K$4,0)))

Yes.. its draggable.. :) you can drag it left or right..
 
You must log in or register to reply here.
Back
Top