# Combination of IF and INDEX/MATCH

1. ## Combination of IF and INDEX/MATCH

Hi team,

I have two worksheets: a reference sheet and an incomplete sheet

Each value in column B of the incomplete sheet lies between the values from one row in columns B and C of the reference sheet. I want to return the values in columns D, E and F from that row of the reference sheet into the incomplete sheet.

To write it all in logical argument, for row 1 of 'Incomplete':
If A1 ('Reference') = 1 (i.e. the value in A1 ('Incomplete')), does B1 ('Incomplete') lie between B1 ('Reference') and C1 ('Reference')
If not, does A2 ('Reference') = 1; if yes, does B1 ('Incomplete') lie between B2 ('Reference') and C2 ('Reference')
and so on, until it finds a match.
Then, copy values from Dx, Ex and Fx of 'Reference' into D1, E1, and F1 of 'Incomplete'.

There should only be one match for each line, so once a match is found, the process can stop and move on to the next row of 'Incomplete' until all rows are matched.

Thus far:
I have tried using VLOOKUP, but the numbers often are not close enough to be a 'near match'.
I have also tried IF (to make sure that only those rows with a value in column A ('Reference') that match the value in column A ('Incomplete') are searched) and INDEX/MATCH . Two problems:
1. I can't seem to get it to work
2. This workbook is a sample only. I have >400 spreadsheets to do this on (each with 10-40 rows). I won't actually know which rows to include in the INDEX/MATCH argument to ensure the value in A (incomplete) matches the value in A (reference)

Would appreciate any help

Kind regards

2. ## Re: Combination of IF and INDEX/MATCH

Ok, there were 24 possible values in column A. I have used COUNTIF on all 400 spreadsheets to calculate the rows containing each, then used a long if/index/match formula to do the rest

(Columns are slightly different due to needing some extra info in the sheet, but you get the idea; had three of these (one for each value I wanted to find), then just used fill down)

=IF(\$I2=1,INDEX(MA!\$F\$2:\$N\$24,MATCH(\$E2,MA!\$F\$2:\$F\$24,1),4),IF(\$I2=2,INDEX(MA!\$F\$25:\$N\$46,MATCH(\$E2,MA!\$F\$25:\$F\$46,1),4),IF(\$I2=3,INDEX(MA!\$F\$47:\$N\$62,MATCH(\$E2,MA!\$F\$47:\$F\$62,1),4),IF(\$I2=4,INDEX(MA!\$F\$63:\$N\$76,MATCH(\$E2,MA!\$F\$63:\$F\$76,1),4),IF(\$I2=5,INDEX(MA!\$F\$77:\$N\$99,MATCH(\$E2,MA!\$F\$77:\$F\$99,1),4),IF(\$I2=6,INDEX(MA!\$F\$100:\$N\$132,MATCH(\$E2,MA!\$F\$100:\$F\$132,1),4),IF(\$I2=7,INDEX(MA!\$F\$133:\$N\$151,MATCH(\$E2,MA!\$F\$133:\$F\$151,1),4),IF(\$I2=8,INDEX(MA!\$F\$152:\$N\$166,MATCH(\$E2,MA!\$F\$152:\$F\$166,1),4),IF(\$I2=9,INDEX(MA!\$F\$167:\$N\$175,MATCH(\$E2,MA!\$F\$167:\$F\$175,1),4),IF(\$I2=10,INDEX(MA!\$F\$176:\$N\$187,MATCH(\$E2,MA!\$F\$176:\$F\$187,1),4),IF(\$I2=11,INDEX(MA!\$F\$188:\$N\$198,MATCH(\$E2,MA!\$F\$188:\$F\$198,1),4),IF(\$I2=12,INDEX(MA!\$F\$199:\$N\$210,MATCH(\$E2,MA!\$F\$199:\$F\$210,1),4),IF(\$I2=13,INDEX(MA!\$F\$211:\$N\$211,MATCH(\$E2,MA!\$F\$211:\$F\$211,1),4),IF(\$I2=14,INDEX(MA!\$F\$212:\$N\$228,MATCH(\$E2,MA!\$F\$212:\$F\$228,1),4),IF(\$I2=15,INDEX(MA!\$F\$229:\$N\$246,MATCH(\$E2,MA!\$F\$229:\$F\$246,1),4),IF(\$I2=16,INDEX(MA!\$F\$247:\$N\$256,MATCH(\$E2,MA!\$F\$247:\$F\$256,1),4),IF(\$I2=17,INDEX(MA!\$F\$257:\$N\$270,MATCH(\$E2,MA!\$F\$257:\$F\$270,1),4),IF(\$I2=18,INDEX(MA!\$F\$271:\$N\$279,MATCH(\$E2,MA!\$F\$271:\$F\$279,1),4),IF(\$I2=19,INDEX(MA!\$F\$280:\$N\$291,MATCH(\$E2,MA!\$F\$280:\$F\$291,1),4),IF(\$I2=20,INDEX(MA!\$F\$292:\$N\$313,MATCH(\$E2,MA!\$F\$292:\$F\$313,1),4),IF(\$I2=21,INDEX(MA!\$F\$314:\$N\$317,MATCH(\$E2,MA!\$F\$314:\$F\$317,1),4),IF(\$I2=22,INDEX(MA!\$F\$318:\$N\$324,MATCH(\$E2,MA!\$F\$318:\$F\$324,1),4),IF(\$I2="X",INDEX(MA!\$F\$325:\$N\$340,MATCH(\$E2,MA!\$F\$325:\$F\$340,1),4),IF(\$I2="Y",INDEX(MA!\$F\$341:\$N\$341,MATCH(\$E2,MA!\$F\$341:\$F\$341,1),4),""))))))))))))))))))))))))

But if anyone has a better suggestion, I'd be happy to hear it for next time!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1