# IF FUNCTION FIRST, THEN WITH INDEX AND MATCH

#### IDRIS86

##### Board Regular
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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### etaf

##### Well-known Member
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

#### IDRIS86

##### Board Regular
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

#### etaf

##### Well-known Member
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

#### IDRIS86

##### Board Regular

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.

#### etaf

##### Well-known Member
=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

#### IDRIS86

##### Board Regular

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.

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

#### etaf

##### Well-known Member
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.
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

#### IDRIS86

##### Board Regular
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.

#### etaf

##### Well-known Member
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.

Replies
20
Views
417
Replies
1
Views
193
Replies
2
Views
158
Replies
5
Views
383
Replies
4
Views
144

1,127,065
Messages
5,622,475
Members
415,897
Latest member
Barry18180

### 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.

### Which adblocker are you using?

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

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