Formula for nested IF statement with multiple Index Match criteria
Hi,
I have used the formula below in my excel 2007 spreadsheet but unfortunately it is not working. Either I have misunderstood how this should work or I have a missing or misplaced bracket or comma.
=IF(AB15=$AD$3,INDEX('contacts to map to ref'!$A$4:$E$5000,MATCH(1,('contacts to map to ref'!$A$4:$A$5000=data!AA15)*('contacts to map to ref'!$C$4:$C$5000=data!I15),0),5),IF(data!AB15=data!$AD$2,INDEX('contacts to map to name'!$A$4:$D$5000,MATCH(1,('contacts to map to name'!$A$4:$A$5000=data!AA15)*('contacts to map to name'!$C$4:$C$5000=data!J15),0),4),IF(data!AB15=data!$AD$1,INDEX('contacts to map to name'!$A$4:$D$5000,MATCH(1,('contacts to map to name'!$A$4:$A$5000=data!AA15)*('contacts to map to name'!$C$4:$C$5000=data!P15),0),4),"")))
Any help gratefully received.
Regards
Caroline
| Attachment | Size |
|---|---|
| query for excel user group re nested if statement with index match.xlsx | 12.28 KB |
Took me a while to dig
Took me a while to dig through this one... I think you're misunderstanding how the MATCH function works. I think you probably want to use VLOOKUP instead.
Can you describe in words what exactly you want in cell H25?
Chris
Could you post your file here?
Files with example data would help a lot.
RE: Could you post your file here?
Hi Almir,
How do I attach an example workbook?
thanks
Caroline
RE: Could you post your file here?
Hi Almir,
How do I attach an example workbook?
thanks
Caroline
Click on "Edit" tab of this post and use File Attachment" option
Click on "Edit" tab of this post and use File Attachment" option.
Formula for nested IF statement with multiple Index Match criter
Hi Almir,
Thank you for your help. Example now attached. What I need is to:
1.put the formula into tab 'data' column H,
2.check column C for a reference number - (the zeros will be blanks in the end document) - where one exists pull that into cell in col H ELSE
3.where a name exists in col D in 'data' tab - check data in col A and col D in 'data' tab match col A and col C in 'contact to map to ref' tab - if yes pull in col E from 'contacts to map to ref' tab ELSE
4.where a name exists in col D in 'data' tab - check data in col A and col D in 'data' tab match col A and col C in 'contact to map to name' - if yes pull in col D from 'contacts to map to name' tab ELSE
5.where col E on 'data' tab is populated - check data in col A and col E match col A and col C in 'contacts to map to ref' tab - if yes pull in col E from 'contacts to map to ref' tab ELSE
6.where col E on 'data' tab is populated - check data in col A and col E match col A and col C in 'contacts to map to name' tab - if yes pull in col D from 'contacts to map to name' tab ELSE
7.leave blank
Am I right to try to use nested IF with Index Match or should I be trying another route?
Thank you in advance again
Regards
Try with nested IFERRORs and VLOOKUP instead of INDEX/MATCH
If I understood you correctly, you check a column and return lookup value from another sheet. If there is not the reference in another sheet, you check another column and return another value and so on. Finally, if there is no reference at all, it should return blank. Is this correct?
If yes, I would try with nested IFERRORs and VLOOKUP instead of INDEX/MATCH. If there is no reference in another sheet, VLOOKUP returns error. You can bypass this by using IFERROR.
Please, confirm or correct my assumptions, so we can continue.
Confirmation of requirements
Hi Almir,
You are almost correct. I check one column and if populated look at the contents of 2 different columns on the same sheet and match those to 2 columns on another sheet and if they match return data from a 3rd column of the second sheet - if no match do the next else statement. Does that help?
adjustment to example workbook
Hi
The workbook would not load so I have changed the formula I was using to text and resaved. Obviously I would add it in correctly using cntl+shift+enter. Also I dont think that I need to reference column G as I was doing originally as that does not do the correct job so I have not referenced col G in my description of my requirements. sorry to confuse things further.
Click on "Edit" tab of this post and use File Attachment" option
Click on "Edit" tab of this post and use File Attachment" option.