IF FUNCTION FIRST, THEN WITH INDEX AND MATCH

I

IDRIS86

Board Regular
Joined
Mar 18, 2021
Messages
82
Office Version
  1. 365
Platform
  1. Windows
Hi,

Below formula works well,

=(INDEX('PRICE LIST'!$C$7368:$C$7679,MATCH(1,(WORKSHEET!O28='PRICE LIST'!$A$7368:$A$7679)*(WORKSHEET!P28='PRICE LIST'!$B$7368:$B$7679),0)))

But I tried adding IF condition at first, but it doesn't work. Highlighted the references added. Please assist.

IF(P27='PRICE LIST'!$B$7368:$B$7679,(INDEX('PRICE LIST'!$C$7368:$C$7679,MATCH(1,(WORKSHEET!O27='PRICE LIST'!$A$7368:$A$7679)*(WORKSHEET!P27='PRICE LIST'!$B$7368:$B$7679),0))),List!$J$21)
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
what is the IF trying to do
P27='PRICE LIST'!$B$7368:$B$7679
are you checking to see if p27 exits in the list 'PRICE LIST'!$B$7368:$B$7679
=Countif('PRICE LIST'!$B$7368:$B$7679,P27)
would return a 1 or more if exists and IF would see that as a true
0 would be false
 
Upvote 0
Hi,

I copied the cells below,

In sheet name WORKSHEET, When cell Q27 is equal to YES, then based on input at O27 & P27, it needs to refer the values from the sheet name PRICE LIST at A7368 to 7679 & B7368 to 7679 to return the value from C7368 to 7679.



Work sheet 7.xlsx
OPQR
26MODELSTYLESLEEVESTYLE MULTI
27 FSD36 A YES #SPILL!
WORKSHEET
Cell Formulas
RangeFormula
R27R27=IF(P27='PRICE LIST'!$B$7368:$B$7679,(INDEX('PRICE LIST'!$C$7368:$C$7679,MATCH(1,(WORKSHEET!O27='PRICE LIST'!$A$7368:$A$7679)*(WORKSHEET!P27='PRICE LIST'!$B$7368:$B$7679),0))),List!$J$21)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
Q27Cell Valuecontains "YES"textNO
Q27Cell Valuecontains "N/A"textNO
Q27Cell Valuecontains "NO"textNO
R27:U27,U28:U33Cell Value=0textNO
Cells with Data Validation
CellAllowCriteria
O27:O33List=INDIRECT(N27)
P27:P33List=IF(NOT(ISERROR(MATCH($N27,List!$B$5:$B$8,0))),INDIRECT(O27&"STYLE"),List!$V$6)
Q27:Q33List=IF(NOT(ISERROR(MATCH($N27,List!$B$5:$B$8,0))),List!$U$5:$U$6,List!$V$6)







Work sheet 7.xlsx
ABC
7367ModelStyleMultiplier
7368SD60A5.00
7369SD60C6.00
7370SD60CO7.00
7371SD60CR8.00
7372SD60WC9.00
7373SD60WO10.00
7374SD60WR11.00
7375SD36A12.00
7376SD36C13.00
7377SD36CO14.00
7378SD36CR15.00
7379SD36WC16.00
7380SD36WO17.00
7381SD36WR18.00
7382SD35A19.00
7383SD35C20.00
7384SD35CO21.00
7385SD35CR22.00
7386SD35WC23.00
7387SD35WO24.00
7388SD35WR25.00
7389SD602A26.00
7390SD602C27.00
7391SD602CO28.00
7392SD602CR29.00
7393SD602WC30.00
7394SD602WO31.00
7395SD602WR32.00
7396SD35SS304A33.00
7397SD35SS304C34.00
7398SD35SS304CO35.00
7399SD35SS304CR36.00
7400SD35SS304WC37.00
7401SD35SS304WO38.00
7402SD35SS304WR39.00
7403SD35SS316A40.00
7404SD35SS316C41.00
7405SD35SS316CO42.00
7406SD35SS316CR43.00
7407SD35SS316WC44.00
7408SD35SS316WO45.00
7409SD35SS316WR46.00
7410SD36SS304A47.00
7411SD36SS304C48.00
7412SD36SS304CO49.00
7413SD36SS304CR50.00
7414SD36SS304WC51.00
7415SD36SS304WO52.00
7416SD36SS304WR53.00
7417SD36SS316A54.00
7418SD36SS316C55.00
7419SD36SS316CO56.00
7420SD36SS316CR57.00
7421SD36SS316WC58.00
7422SD36SS316WO59.00
7423SD36SS316WR60.00
7424FSD60A61.00
7425FSD60C62.00
7426FSD60CO63.00
7427FSD60CR64.00
7428FSD60LO65.00
7429FSD60R66.00
7430FSD60WC67.00
7431FSD60WO68.00
7432FSD60WR69.00
7433FSD60MA70.00
7434FSD60MC71.00
7435FSD60MCO72.00
7436FSD60MCR73.00
7437FSD60MLO74.00
7438FSD60MR75.00
7439FSD60MWC76.00
7440FSD60MWO77.00
7441FSD60MWR78.00
7442FSD602A79.00
7443FSD602C80.00
7444FSD602CO81.00
7445FSD602CR82.00
7446FSD602LO83.00
7447FSD602R84.00
7448FSD602WC85.00
7449FSD602WO86.00
7450FSD602WR87.00
7451FSD603A88.00
7452FSD603C89.00
7453FSD603CO90.00
7454FSD603CR91.00
7455FSD603LO92.00
7456FSD603R93.00
7457FSD603WC94.00
7458FSD603WO95.00
7459FSD603WR96.00
7460FSD603MA97.00
7461FSD603MC98.00
7462FSD603MCO99.00
7463FSD603MCR100.00
7464FSD603MLO101.00
7465FSD603MR102.00
7466FSD603MWC103.00
7467FSD603MWO104.00
7468FSD603MWR105.00
7469FSD60VA106.00
7470FSD60VC107.00
7471FSD60VCO108.00
7472FSD60VCR109.00
7473FSD60VLO110.00
7474FSD60VR111.00
7475FSD60VWC112.00
7476FSD60VWO113.00
7477FSD60VWR114.00
7478FSD36A115.00
7479FSD36C116.00
7480FSD36CO117.00
7481FSD36CR118.00
7482FSD36LO119.00
7483FSD36R120.00
7484FSD36WC121.00
7485FSD36WO122.00
7486FSD36WR123.00
7487FSD35A124.00
7488FSD35C125.00
7489FSD35CO126.00
7490FSD35CR127.00
7491FSD35LO128.00
7492FSD35R129.00
7493FSD35WC130.00
7494FSD35WO131.00
7495FSD35WR132.00
PRICE LIST
Cell Formulas
RangeFormula
C7369:C7495C7369=C7368+1
 
Upvote 0
does this work
=IF(R27="YES",INDEX('Price List'!C2:C1000,MATCH(P27&Q27,'Price List'!A2:A1000&'Price List'!B2:B1000,0)))

I had to remove all the spaces , leading and trailing, is that in the original data ?

Book2
PQRS
25
26MODELSTYLESLEEVESTYLE MULTI
27FSD36AYESTEST 1
Sheet1
Cell Formulas
RangeFormula
S27S27=IF(R27="YES",INDEX('Price List'!C2:C1000,MATCH(P27&Q27,'Price List'!A2:A1000&'Price List'!B2:B1000,0)))


Book2
ABC
102FSD603MWR1
103FSD60VA1
104FSD60VC1
105FSD60VCO1
106FSD60VCR1
107FSD60VLO1
108FSD60VR1
109FSD60VWC1
110FSD60VWO1
111FSD60VWR1
112FSD36ATEST 1
113FSD36C1
114FSD36CO1
115FSD36CR1
116FSD36LO1
117FSD36R1
118FSD36WC1
119FSD36WO1
Price List
Cell Formulas
RangeFormula
C102:C111,C113:C119C102=C7467+1
 
Upvote 0
Solution
Hi It works. Thanks. When R27=NA & NO, it shows the output as FALSE. At these conditions I would need the output should be zero (0). Would you assist.
 
Upvote 0
=IF(R27="YES",INDEX('Price List'!C2:C1000,MATCH(P27&Q27,'Price List'!A2:A1000&'Price List'!B2:B1000,0)),0)

what do you want if it cannot match - you get #N/A

an IFERROR may help
=IFERROR(IF(R27="YES",INDEX('Price List'!C2:C1000,MATCH(P27&Q27,'Price List'!A2:A1000&'Price List'!B2:B1000,0)),0),"")
will give a blank if no match - change "" to zero , if thats the result you want
 
Upvote 0
Thanks a lot. it works. I am also looking for multiple IF condition in DATA validation. Would you able to assist.

The requirement is,

1. When I27 is equal to YES
a) and J27 equal to GALV.STEEL in worksheet, then output at K27 in worksheet should come with dropdown of list provided at B7800 to B7814 at sheet price list.
b) and J27 equal to SS304 or SS316 in worksheet, then output at K27 in worksheet should come with dropdown of list provided at D7800 to D7808 at sheet price list.

2. When I27 is equal to NO or NA, then K27 should come with NA.

Please assist.

worksheet 8.xlsx
IJK
26SLEEVESLEEVE MATERIALSLEEVE TYPE
27 YES GALV.STEEL 20 IN 20 GA
WORKSHEET
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I27:J27Cell Valuecontains "YES"textNO
I27:J27Cell Valuecontains "N/A"textNO
I27:J27Cell Valuecontains "NO"textNO
Cells with Data Validation
CellAllowCriteria
I27:J33List=IF(NOT(ISERROR(MATCH($D27,List!$B$5:$B$8,0))),List!$U$5:$U$6,List!$V$6)
K27:K33List=IF($I27="YES",List!$W$5:$W$22,IF($I27="NO",List!$V$5,IF($I27="NA",List!$V$6)))





worksheet 8.xlsx
BCDE
7799GISS
78009IN 20GA17IN 20GA
78019IN 18GA17IN 18GA
78029IN 16GA17IN 16GA
780312IN 20GA20IN 20GA
780412IN 18GA20IN 18GA
780512IN 16GA20IN 16GA
780614IN 20GA24IN 20GA
780714IN 18GA24IN 18GA
780814IN 16GA24IN 16GA
780916IN 20GA
781016IN 18GA
781116IN 16GA
781220IN 20GA
781320IN 18GA
781420IN 16GA
PRICE LIST
 
Upvote 0
quite a complicated process - using named ranges in LIST data validation
I have used
=IF(AND(I27="yes",J27="GALV.STEEL"),B_7800,IF(AND(I27="yes",OR(J27="SS304",J27="SS316")),D_7800,""))
And named the ranges
Define Named Ranges
B7800 to B7814 = B_7800
D7800 to D7808 = D_7800
Added a NA cell named N

So the first part work, Just now need to figure out the last part

Which If i turn off the error to show NA
2. When I27 is equal to NO or NA, then K27 should come with NA.
Click to expand...
Which then stops the first part changing , untill the drop down is used

so i made a test sheet - using different ranges fro simplicity - B and D
and put the spreadsheet on dropbox

Dropbox - File Deleted - Simplify your life

www.dropbox.com www.dropbox.com
 
Upvote 0
Hi Thanks. But I couldn't find the formula doesn't have the conditions at NA or NO at I27.

I downloaded your excel file. But I don't find the formula for this request. Can see the formula of earlier request.
 
Upvote 0
i did say still working it ou
So the first part work, Just now need to figure out the last part

Which If i turn off the error to show NA
2. When I27 is equal to NO or NA, then K27 should come with NA.
Click to expand...
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question
Error- after multiplication shows incorrect value
Replies
2
Views
229
Fluff
Fluff
T
  • Question
Lookup within a lookup
2
Replies
10
Views
204
TimeForTim
T
R
  • Question
Match Lookup Formula
Replies
3
Views
222
awoohaw
awoohaw
A
  • Solved
#REF! error when using INDEX MATCH
Replies
5
Views
81
Fluff
Fluff
J
  • Question
LARGE function within an index Match (No duplicates)
Replies
7
Views
148
Fluff
Fluff

Forum statistics

Threads
1,214,641
Messages
6,120,695
Members
448,979
Latest member
DET4492

Share this page

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