VlookUp

[pjandshelly]

I have a table with multiple columns. I am wanting to search one of those columns to find if there is a value > 0. If so, I want it to return back to a main table dashboard that value in one column and in the second column, it will return the number in the column it was searching. It would also rank the values with ties.

For example:

A B C
Test No 1
Stop Yes 0
Now No 5

Dashboard returns
Value Total
Now 5
Test 1

 

[Fluff]

How about

Book1
	A	B	C
2	test1		1
3	test2		2
4	test3		0
5	test4		4
6	test5		0
7	test6		6
8	test7		7
9	test8		0
10	test9		9
11	test10		0
work

and

Book1
	A	B
1	Value	Total
2	test1	1
3	test2	2
4	test4	4
5	test6	6
6	test7	7
7	test9	9
Lookup

Cell Formulas
Range	Formula
A2	=IFERROR(INDEX(work!$A$2:$A$11,AGGREGATE(15,6,(ROW(work!$A$2:$A$11)-ROW(work!$A$2)+1)/(work!$C$2:$C$11>0),ROWS($A$2:$A2))),"")
B2	=IFERROR(INDEX(work!$C$2:$C$11,MATCH(A2,work!$A$2:$A$11,0)),"")

 

[pjandshelly]

So for the first code, I get the expected results. The second code, I am getting 0s.

My code is =IFERROR(INDEX($AM$52:$AM$164,MATCH(I52,$I$52:$I$164,0)),"").

My AM Column is your C Column and My I column is your A Column.

 

[Fluff]

You don't have the sheet name in that formula, are you entering the formula on the same sheet as the data?

 

[pjandshelly]

Yes

 

[pjandshelly]

Everything is on the same page. The underlying data is at the bottom of the page with the dashboard showing at the top of the page.

 

[Fluff]

In that case where did you put the first formula, that I had in A2?

 

[pjandshelly]

I had it where I wanted my values from column A to go in my dashboard. Both formulas are at the top of the page.

 

[Fluff]

What cell did you put it in?

 

[pjandshelly]

I put the second formula in cell AG21. The first formula for Column A was put in a merged column from AA21:AF21.