IF and VLOOKUP statement - not working

mandiedwards0320

New Member
Joined
Jun 18, 2013
Messages
8
Hello Everyone!

I am looking to create a formula to do the following..

If C14="THIS" then look in table on page 2 (columns B-F) for data in Cell E23(from page 1) and return pricing listed in column 3, but if Cell C14="THAT" then look in table on page 2 (columns B-F) for data in Cell E23 and return pricing in column 5.

I had a working formula using if statements and vlookups for this, but my computer crashed and I cannot recover the original worksheet and cannot remember how I came up with the formula.

Any help would be greatly appreciated.

Thanks!
Mandi
 

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.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,920
Office Version
  1. 365
Platform
  1. Windows
Maybe
=INDEX(Sheet2!B2:F25,MATCH(E23,Sheet2!$B$2:$B$25,0),IF(C14="This",3,5))
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,808
Office Version
  1. 365
Platform
  1. Windows
Maybe:
Change ranges to match your data.

=IF(C14="This",VLOOKUP($E$23,Sheet2!$B$1:$F$25,3,0),VLOOKUP($E$23,Sheet2!$B$1:$F$25,5,0))


<colgroup><col width="698"></colgroup><tbody>
</tbody>
 

mandiedwards0320

New Member
Joined
Jun 18, 2013
Messages
8

ADVERTISEMENT

Maybe:
Change ranges to match your data.

=IF(C14="This",VLOOKUP($E$23,Sheet2!$B$1:$F$25,3,0),VLOOKUP($E$23,Sheet2!$B$1:$F$25,5,0))


<tbody>
</tbody>

No , this returns a "N/A" response.
 

mandiedwards0320

New Member
Joined
Jun 18, 2013
Messages
8
Hello Everyone!

I am looking to create a formula to do the following..

If C14="THIS" then look in table on page 2 (columns B-F) for data in Cell E23(from page 1) and return pricing listed in column 3, but if Cell C14="THAT" then look in table on page 2 (columns B-F) for data in Cell E23 and return pricing in column 5.

I had a working formula using if statements and vlookups for this, but my computer crashed and I cannot recover the original worksheet and cannot remember how I came up with the formula.

Any help would be greatly appreciated.

Thanks!
Mandi




This is the formula I use now-

=IF(ISNA(VLOOKUP(E23,'Price Sheet'!$B$2:$D$227,3,0))=TRUE,"$0.00,VLOOKUP(E23,'Price Sheet'!$B$2:$D$227,3))*H26

I just need to add the C14 component of if this or that and 2 additional columns to the array table on the price sheet page.
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,808
Office Version
  1. 365
Platform
  1. Windows
It would help if you would post a small sample of your data.
Is the look up value in E23 in column B of sheet 2?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,920
Office Version
  1. 365
Platform
  1. Windows
Maybe
=IFERROR(INDEX('Price Sheet'!$B$2:$F$227,MATCH(E23,'Price Sheet'!$B$2:$B$227,0),IF(C14="This",3,5)),0)*H26
 

Forum statistics

Threads
1,136,323
Messages
5,675,092
Members
419,549
Latest member
EliteBeat

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
Top