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

Baraines57

New Member
Joined
Jan 21, 2019
Messages
2
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
I apologize - here is a better picture of my spreadsheet:

NameTypeOrangesApplesPlumsCherries
Joe Smith$25.00
Betsy Fine$10.00
George Jones$5.00
Wendy Wonder$50.00

<colgroup><col span="2"><col span="4"></colgroup><tbody>
</tbody>

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
 
Upvote 0
A
B
C
D
E
F
1
Name
TypeOrangesApplesPlumsCherries
2
Joe Smith$25.00
3
Betsy Fine$10.00
4
George Jones$5.00
5
Wendy Wonder$50.00

<tbody>
</tbody>

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
 
Last edited:
Upvote 0
ABCDEF
1NameTypeOrangesApplesPlumsCherries
2Joe Smith$25.00
3Betsy Fine$10.00
4George Jones$5.00
5Wendy Wonder$50.00

<tbody>
</tbody>

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
 
Upvote 0
Hi,

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


Book1
ABCDEF
1NameTypeOrangesApplesPlumsCherries
2Joe SmithPlums$25.00
3Betsy FineApples$10.00
4George JonesOranges$5.00
5Wendy WonderCherries$50.00
Sheet485
Cell Formulas
RangeFormula
B2=LOOKUP(2,1/C2:F2,C$1:F$1)
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top