Help with INDEX/MATCH and multiple cells

[Fillonte]

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)

 

[Fillonte]

Exactly! (if you are checking against "Sheet1!A4" and "Sheet1!A5")

 

[yesterdays]

I see, I'm working on it now, although it look a bit hard ( for me of course), will get back to you when I'm done

 

[Fillonte]

Thank you so much for your help, it's truly appreciated.

 

[yesterdays]

I need you to clarify some thing, the ACTIVITIES in Workbook2-sheet1, they will always have a match in Workbook1-sheet1 right?

 

[Fillonte]

Yes they would, since Workbook1-sheet1 is a list of all the activities we perform in general and Workbook2-sheet1 merely reports what has been done in a single patient.

 

[yesterdays]

hm... Look like i can't help you now(this problem is hard =_=), and I have to leave now, will get back to you tomorrow(it's 18h here), hope someone else can help you in the meantime, sorry ...

 

[Fillonte]

No worries and thanks a lot anyway. Please do get back here if you have time tomorrow.

In the meantime, anyone else with any insight?

 

[Aladin Akyurek]

No worries and thanks a lot anyway. Please do get back here if you have time tomorrow.

In the meantime, anyone else with any insight?

wb1.xlsx, Sheet1

Row\Col	A​	B​	C​
1​	SERVICE	PRICE	TYPE
2​	Visit	50​	Ambulatory
3​	Abdominal Surgery	300​	Surgery
4​	Anesthetic	200​	Ambulatory
5​	Pelvic Surgery	350​	Surgery

wb2.xlsx, Sheet1

Row\Col	A​	B​	C​
1​	ACTIVITY	DATE	NOTES
2​	Medication
3​	Echography
4​	Pelvic Surgery
5​	Anesthetic

wb2.xlsx, Sheet2

Row\Col	A​	B​
1​	Report Module
2​
3​	Therapy
4​
5​	Activity
6​
7​	Diagnosis
8​

What are the desired results, that is, the values you want to see and where? Note that I'm not asking for the formulas you tried...

 

[Fillonte]

Hi, ideally I'd like to see it like this the red text

wb1.xlsx, Sheet1

Row\Col	A​	B​	C​
1​	SERVICE	PRICE	TYPE
2​	Visit	50​	Ambulatory
3​	Abdominal Surgery	300​	Surgery
4​	Anesthetic	200​	Ambulatory
5​	Pelvic Surgery	350​	Surgery

<tbody>
</tbody>

wb2.xlsx, Sheet1

Row\Col	A​	B​	C​
1​	ACTIVITY	DATE	NOTES
2​	Medication
3​	Echography
4​	Pelvic Surgery
5​	Anesthetic

<tbody>
</tbody>

wb2.xlsx, Sheet2

Row\Col	A​	B​
1​	Report Module
2​
3​	Therapy
4​
5​	Activity	Ambulatory
6​		Surgery
7​	Diagnosis
8​

<tbody>
</tbody>

(the original tables have a lot more rows and columns but it boils down to this)

That means that I need a way to check the values in the cells A2:A5 of wb2.xlsx, Sheet1 and see if any of them matches with the values in A2:A5 of wb1.xlsx, Sheet 1 and report in B5:B6 of wb2.xlsx, Sheet2 the corresponding values from Column C of wb1.xlsx, Sheet1. Note that in this example wb1.xlsx, Sheet1 and wb2.xlsx, Sheet 1 have the same number of rows whilst in my original workbooks the first one has a lot more than the latter (if this might be of any relevance).

Does this make any sense? I know it might sound a bit convoluted but it's rather simple to achieve if I'd just want to check a single cell of wb2.xlsx, Sheet1 instead of 5 and I don't know why it gets so complicated with more (and that is probably because I have zero programming background)

Thanks a lot for the help.

 

[Aladin Akyurek]

Hi, ideally I'd like to see it like this the red text

[...]
wb2.xlsx, Sheet2

Row\Col	A​	B​
1​	Report Module
2​
3​	Therapy
4​
5​	Activity	Ambulatory
6​		Surgery
7​	Diagnosis
8​

<tbody>
</tbody>

(the original tables have a lot more rows and columns but it boils down to this)

That means that I need a way to check the values in the cells A2:A5 of wb2.xlsx, Sheet1 and see if any of them matches with the values in A2:A5 of wb1.xlsx, Sheet 1 and report in B5:B6 of wb2.xlsx, Sheet2 the corresponding values from Column C of wb1.xlsx, Sheet1. Note that in this example wb1.xlsx, Sheet1 and wb2.xlsx, Sheet 1 have the same number of rows whilst in my original workbooks the first one has a lot more than the latter (if this might be of any relevance).

Does this make any sense? I know it might sound a bit convoluted but it's rather simple to achieve if I'd just want to check a single cell of wb2.xlsx, Sheet1 instead of 5 and I don't know why it gets so complicated with more (and that is probably because I have zero programming background)

Thanks a lot for the help.

wb2.xlsx, Sheet2

B5, control+shift+enter, not just enter, and copy across:

=IFERROR(INDEX([wb1.xlsx]Sheet1!$C$2:$C$5,
    SMALL(IF(ISNUMBER(MATCH(Sheet1!$A$2:$A$5,[wb1.xlsx]Sheet1!$A$2:$A$5,0)),
    ROW([wb1.xlsx]Sheet1!$A$2:$A$5)-ROW([wb1.xlsx]Sheet1!$A$2)+1),
    COLUMNS($B$5:B5))),"")