Thread: IF and VLOOKUP to find column header for values in range greater than zero Thanks:  2 Post #5211934 (1)Post #5211918 (1) Likes:  2 Post #5211934 (1)Post #5211918 (1)

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

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.

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

2. Re: IF and VLOOKUP to find column header for values in range greater than zero

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

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

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

3. Re: IF and VLOOKUP to find column header for values in range greater than zero

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

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

4. Re: IF and VLOOKUP to find column header for values in range greater than zero

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

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

5. Re: IF and VLOOKUP to find column header for values in range greater than zero

Hi,

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

ABCDEF
1NameTypeOrangesApplesPlumsCherries
2Joe SmithPlums\$25.00
3Betsy FineApples\$10.00
4George JonesOranges\$5.00
5Wendy WonderCherries\$50.00

Sheet485

Worksheet Formulas
CellFormula
B2=LOOKUP(2,1/C2:F2,C\$1:F\$1)