Help with INDEX/MATCH and multiple cells

Fillonte

Board Regular
Joined
May 29, 2015
Messages
73
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)
 
Last edited:

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

yesterdays

Active Member
Joined
Oct 8, 2014
Messages
337

ADVERTISEMENT

or just a table of data you made up.
 

yesterdays

Active Member
Joined
Oct 8, 2014
Messages
337
Row\Col
A​
B​
C​
D​
E​
F​
G​
1​
Name of ServicePricetypeName of ServiceType 1Type 2
2​
Bath
1​
ABathAD
3​
Hair Cut
2​
BHair CutBE
4​
Massage
3​
CMassageCF
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?
 

Fillonte

Board Regular
Joined
May 29, 2015
Messages
73

ADVERTISEMENT

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

Thanks for your time yesterdays
 

yesterdays

Active Member
Joined
Oct 8, 2014
Messages
337
The forum doesn't allow us to attach files here, you could upload it to another source and post the link here
 

Fillonte

Board Regular
Joined
May 29, 2015
Messages
73
In case I can't attach files...
SERVICEPRICETYPE
Visit50Ambulatory
Abdominal Surgery300Surgery
Anesthetic200Ambulatory
Pelvic Surgery350Surgery

<tbody>
</tbody>
Workbook1-sheet1

ACTIVITYDATENOTES
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
 

yesterdays

Active Member
Joined
Oct 8, 2014
Messages
337
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​
ACTIVITYDATENOTES
4​
Anesthetic​
200​
Ambulatory​
Medication
5​
Pelvic Surgery​
350​
Surgery​
Echography
6​
Workbook2-sheet1Pelvic Surgery
7​
Anesthetic
8​
9​
10​
11​
Workbook2-sheet2
12​
Report Module
13​
Therapy
14​
Activity
Surgery
15​
Ambulatory

<tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,129,593
Messages
5,637,294
Members
416,963
Latest member
zazama

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top