IF and VLOOKUP to find column header for values in range greater than zero

[Baraines57]

I am trying to setup an IF statement using a VLOOKUP to find a value in a range greater than zero and return that column's header label. I have a list of names and I am trying to find the first column that has an actual dollar amount instead of blank and then return that column's header label.

This is like my spreadsheet:

Name Oranges Apples Plums Cherries
Joe Smith $25.00
Betsy Fine $10.00
George Jones $5.00
Wendy Wonder $50.00


I want my formula to give me "Plums" for Joe Smith, "Apples" for Betsy Fine, "Oranges" for George Jones and "Cherries" for Wendy Wonder.

Please advise - I know this is probably very simple but I haven't done this in a while so I appreciate the assistance!​

Thanks!
Betty Raines

 

[Baraines57]

I apologize - here is a better picture of my spreadsheet:

[TABLE="width: 474"]
<colgroup><col span="2"><col span="4"></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Type[/TD]
[TD]Oranges[/TD]
[TD]Apples[/TD]
[TD]Plums[/TD]
[TD]Cherries[/TD]
[/TR]
[TR]
[TD]Joe Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$25.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Betsy Fine[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$10.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]George Jones[/TD]
[TD][/TD]
[TD="align: right"]$5.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wendy Wonder[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$50.00[/TD]
[/TR]
</tbody>[/TABLE]

Under the heading "Type" I want to put my formula and have it return the column header or type of fruit for which the person has paid $. Please advise.

Thanks!
Betty Raines

 

[mail2master]

[TABLE="class: grid, width: 474"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]Name
[/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]Oranges[/TD]
[TD="align: center"]Apples[/TD]
[TD="align: center"]Plums[/TD]
[TD="align: center"]Cherries[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD]Joe Smith[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]$25.00[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD]Betsy Fine[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]$10.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD]George Jones[/TD]
[TD="align: center"][/TD]
[TD="align: center"]$5.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD]Wendy Wonder[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]$50.00[/TD]
[/TR]
</tbody>[/TABLE]

Formula in B2: =INDEX($C$1:$F$1,1,SMALL(IF(C2:F2<>"",COLUMN(C2:F2)-COLUMN($C$2)+1),1))
Confirm with Ctrl+Shift+Enter

 

[mail2master]

[TABLE="class: grid, width: 474"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Name[/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]Oranges[/TD]
[TD="align: center"]Apples[/TD]
[TD="align: center"]Plums[/TD]
[TD="align: center"]Cherries[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Joe Smith[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]$25.00[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Betsy Fine[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]$10.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]George Jones[/TD]
[TD="align: center"][/TD]
[TD="align: center"]$5.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Wendy Wonder[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]$50.00[/TD]
[/TR]
</tbody>[/TABLE]

Formula in B2: =INDEX($C$1:$F$1,1,SMALL(IF(C2:F2<>"",COLUMN(C2:F2)-COLUMN($C$2)+1),1))
Confirm with Ctrl+Shift+Enter

If you want to stick with VLOOKUP & IF, try this:

=VLOOKUP($A$1,$A$1:$F$1,SMALL(IF(C2:F2<>"",COLUMN(C2:F2)-COLUMN($C$2)+3),1),0)
Confirm with Ctrl+Shift+Enter

 

[jtakw]

Hi,

Based on your sample, this would also work, normally entered and copied down:

Book1
	A	B	C	D	E	F
1	Name	Type	Oranges	Apples	Plums	Cherries
2	Joe Smith	Plums			$25.00
3	Betsy Fine	Apples		$10.00
4	George Jones	Oranges	$5.00
5	Wendy Wonder	Cherries				$50.00
Sheet485

Cell Formulas
Range	Formula
B2	=LOOKUP(2,1/C2:F2,C$1:F$1)