Hi,
I'm pretty new to Excel (as in a real beginner) and up to now I've been able to use google and this forum to learn new formulas and macros and address the problems I've encountered. However, I seem to have come across an issue I'm not able to overcome with some simple internet digging.
I have 2 workbooks, one of which has multiple sheets and is basically the one I'm trying to set up and the other one being a simple rate table (just 3 columns, "name of the service", "price" and "type").
What I'm trying to do is to create a macro that from my first workbook looks across two sheets and based on the value found can lookup the price table and find the "type" corresponding to the service listed. And this appears to be the easy bit and the one I've managed to get through using this INDEX/MATCH formula:
=INDEX('[TARIFFE ATTIVITÀ.xlsm]Foglio1'!$A$2:$C$166;MATCH(Cartella!A27;'[TARIFFE ATTIVITÀ.xlsm]Foglio1'!$A$2:$A$166;0);3)
(I apologise for the formula not being in English but I suppose the names don't really matter)
However this is just part of what I'm trying to do, since this formula only checks the MATCH for one cell "Cartella!A27", while I need it to check from Cartella!A27 to Cartella!A40 for the same values and when it'd find a match it'd stop checking for more.
I would then have another cell next to it that would do basically the same as the first one but would list a possible second match. And so on. These subsequent cells would have an =IF to never duplicate the same results of the previous ones.
As much as I've tried to change it and use other commands I haven't managed to make it work. The only way I've been able to do it has been by having an =IF(AND(OR formula that would list all the cells and all the possible "services" but it started being ridiculously long very quickly and it would take days to write down.
Any input on how to solve this would be really appreciated.
Many thanks in advance.
(all the values in the cells are text)
I'm pretty new to Excel (as in a real beginner) and up to now I've been able to use google and this forum to learn new formulas and macros and address the problems I've encountered. However, I seem to have come across an issue I'm not able to overcome with some simple internet digging.
I have 2 workbooks, one of which has multiple sheets and is basically the one I'm trying to set up and the other one being a simple rate table (just 3 columns, "name of the service", "price" and "type").
What I'm trying to do is to create a macro that from my first workbook looks across two sheets and based on the value found can lookup the price table and find the "type" corresponding to the service listed. And this appears to be the easy bit and the one I've managed to get through using this INDEX/MATCH formula:
=INDEX('[TARIFFE ATTIVITÀ.xlsm]Foglio1'!$A$2:$C$166;MATCH(Cartella!A27;'[TARIFFE ATTIVITÀ.xlsm]Foglio1'!$A$2:$A$166;0);3)
(I apologise for the formula not being in English but I suppose the names don't really matter)
However this is just part of what I'm trying to do, since this formula only checks the MATCH for one cell "Cartella!A27", while I need it to check from Cartella!A27 to Cartella!A40 for the same values and when it'd find a match it'd stop checking for more.
I would then have another cell next to it that would do basically the same as the first one but would list a possible second match. And so on. These subsequent cells would have an =IF to never duplicate the same results of the previous ones.
As much as I've tried to change it and use other commands I haven't managed to make it work. The only way I've been able to do it has been by having an =IF(AND(OR formula that would list all the cells and all the possible "services" but it started being ridiculously long very quickly and it would take days to write down.
Any input on how to solve this would be really appreciated.
Many thanks in advance.
(all the values in the cells are text)
Last edited: