# Help with INDEX/MATCH and multiple cells

#### Fillonte

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

(all the values in the cells are text)

Last edited:

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Anyone, please? Sorry for the shameless bump but I'm really stuck.

Could you post a sample of what you have and what you need it to be?

You mean attach the Excel file?

or just a table of data you made up.

 Row\Col A​ B​ C​ D​ E​ F​ G​ 1​ Name of Service Price type Name of Service Type 1 Type 2 2​ Bath 1​ A Bath A D 3​ Hair Cut 2​ B Hair Cut B E 4​ Massage 3​ C Massage C F 5​ Bath 4​ D 6​ Hair Cut 5​ E 7​ Massage 6​ F 8​

<tbody>
</tbody>

ok let see if I understand correctly, what you have is in range A1:C7, What you need is the formula in F2:G4, right?

I feel rather dumb right now, but how do I attach a file here? (I've created an example workbook for clarity)

The forum doesn't allow us to attach files here, you could upload it to another source and post the link here

In case I can't attach files...
 SERVICE PRICE TYPE Visit 50 Ambulatory Abdominal Surgery 300 Surgery Anesthetic 200 Ambulatory Pelvic Surgery 350 Surgery

<tbody>
</tbody>
Workbook1-sheet1

 ACTIVITY DATE NOTES Medication Echography Pelvic Surgery Anesthetic

<tbody>
</tbody>
Workbook2-sheet1

 Report Module Therapy Activity =INDEX([Workbook1.xlsx]Sheet1!\$A\$2:\$C\$5;MATCH(Sheet1!A4;[Workbook1.xlsx]Sheet1!\$A\$2:\$A\$5;0);3) Diagnosis

<tbody>
</tbody>
Workbook2-sheet2

Ok this is basically what I have: what I need is that formula there to check not just for a Match in "Sheet1!A4" but more like in "Sheet1!A2:A5" (although that doesn't work) and report the first match it finds. In the Cell below "Activity" in "Workbook2-sheet2" I'd have the same formula to check for another match in the same cells and so on until I find no more matches.

Hope this clears it up

Thanks

so the result should look like this?: (the red text)
 Row\Col A​ B​ C​ D​ E​ F​ G​ 1​ SERVICE​ PRICE​ TYPE​ Workbook2-sheet1 2​ Visit​ 50​ Ambulatory​ 3​ Abdominal Surgery​ 300​ Surgery​ ACTIVITY DATE NOTES 4​ Anesthetic​ 200​ Ambulatory​ Medication 5​ Pelvic Surgery​ 350​ Surgery​ Echography 6​ Workbook2-sheet1 Pelvic Surgery 7​ Anesthetic 8​ 9​ 10​ 11​ Workbook2-sheet2 12​ Report Module 13​ Therapy 14​ Activity Surgery​ 15​ Ambulatory​

<tbody>
</tbody>

Replies
13
Views
340
Replies
3
Views
129
Replies
2
Views
334
Replies
5
Views
208
Replies
1
Views
203

1,203,485
Messages
6,055,685
Members
444,807
Latest member
RustyExcel

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