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]

Thanks a lot, the formula works. Unfortunately, strangely enough, whilst it works perfectly with the example workbooks I had created to attach here (before I realised I can't attach files), which share the exact same names of my example and the ones you have put in the formula, it doesn't seem to work with my original files. I've copied yours and switched all the names and row/column numbers to match the relevant ones in those workbooks, but the result is an empty cell.

What could I have done wrong?

(worth noting, I'm on a Mac and I had to change all the "," with ";" but this didn't cause any problem with the example wb, so I don't think that's the issue)

 

[Aladin Akyurek]

Thanks a lot, the formula works. Unfortunately, strangely enough, whilst it works perfectly with the example workbooks I had created to attach here (before I realised I can't attach files), which share the exact same names of my example and the ones you have put in the formula, it doesn't seem to work with my original files. I've copied yours and switched all the names and row/column numbers to match the relevant ones in those workbooks, but the result is an empty cell.

What could I have done wrong?

(worth noting, I'm on a Mac and I had to change all the "," with ";" but this didn't cause any problem with the example wb, so I don't think that's the issue)

See the implementation files: https://dl.dropboxusercontent.com/u/65698317/wb1.xlsx and https://dl.dropboxusercontent.com/u/65698317/wb2.xlsx.

 

[Fillonte]

Yes that's what I had done with my examples files to test it out. The original files though won't work even when swapping the relevant names.

However I think I figured out what's wrong (even though I don't really understand the reason behind it).
In the example wbs the cells are A2:A5 both in the wb1 and wb2, whilst in the file I'm trying to set up the cells in wb1 are A2:A166 and in wb2 they are A27:A40. I've tried to change it and pretend I have just A2:A5 in both my original wbs and it worked, but I can't really do it with the final version.

Is there any way to do it like it is (A2:A166 in the 1st wb and A27:A40 in the second one)? I don't think I can get the same amount of cells or put them in the same position in both the wbs.

As always, thanks a lot for your help.

 

[Aladin Akyurek]

Yes that's what I had done with my examples files to test it out. The original files though won't work even when swapping the relevant names.

However I think I figured out what's wrong (even though I don't really understand the reason behind it).
In the example wbs the cells are A2:A5 both in the wb1 and wb2, whilst in the file I'm trying to set up the cells in wb1 are A2:A166 and in wb2 they are A27:A40. I've tried to change it and pretend I have just A2:A5 in both my original wbs and it worked, but I can't really do it with the final version.

Is there any way to do it like it is (A2:A166 in the 1st wb and A27:A40 in the second one)? I don't think I can get the same amount of cells or put them in the same position in both the wbs.

As always, thanks a lot for your help.

wb2.xlsx, Sheet2

B5, control+shift+enter and copy across:

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

See the refreshed files...

 

[Fillonte]

Great! It works! That's amazing (especially since I have no idea how).

I just had to change to names to match the original worksheets and it worked perfectly. Thank you so much, I really appreciate your help, you definitely saved the day.

Just a quick follow up question: I'm now putting the same formula into more than one cell as you also did in the example wbs. Is there a way to do the same not just for columns but also for rows? For example, in the wbs you sent me, I'd like to have the formula work in B5, C5, B6 and C6 (I can make it work for different rows since I think I understood what to change, but I can't do the same with columns, as in I could put it successfully in D5, E5... but not in B6).

Many many thanks again Aladin.

 

[Aladin Akyurek]

Great! It works! That's amazing (especially since I have no idea how).

I just had to change to names to match the original worksheets and it worked perfectly. Thank you so much, I really appreciate your help, you definitely saved the day.

Just a quick follow up question: I'm now putting the same formula into more than one cell as you also did in the example wbs. Is there a way to do the same not just for columns but also for rows? For example, in the wbs you sent me, I'd like to have the formula work in B5, C5, B6 and C6 (I can make it work for different rows since I think I understood what to change, but I can't do the same with columns, as in I could put it successfully in D5, E5... but not in B6).

Many many thanks again Aladin.

Rowwise output...

wb2.xlsx, Sheet2

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

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

 

[Fillonte]

Perfect!

Thank you so much again.

One last question, is there a way to automatically avoid showing 2 times the same values in the cells that use the formula you gave me?

Many many thanks.

You are called Aladin, but to me you've been a lot more like the genius in the bottle.

 

[Aladin Akyurek]

Perfect!

Thank you so much again.

You are welcome.

One last question, is there a way to automatically avoid showing 2 times the same values in the cells that use the formula you gave me?

Many many thanks.

I gather you don't want to see an entry/result like Ambulatory more than once if at all... If so:

=IFERROR(INDEX([wb1.xlsx]Sheet1!$C$2:$C$166, SMALL(IF(FREQUENCY(IF([wb1.xlsx]Sheet1!$C$2:$C$166<>"",
IF(ISNUMBER(MATCH([wb1.xlsx]Sheet1!$A$2:$A$166,Sheet1!$A$27:$A$40,0)),
MATCH([wb1.xlsx]Sheet1!$C$2:$C$166,[wb1.xlsx]Sheet1!$C$2:$C$166,0))),
ROW([wb1.xlsx]Sheet1!$A$2:$A$166)-ROW([wb1.xlsx]Sheet1!$A$2)+1),
ROW([wb1.xlsx]Sheet1!$A$2:$A$166)-ROW([wb1.xlsx]Sheet1!$A$2)+1),
ROWS($B$5:B5))),"")

You are called Aladin, but to me you've been a lot more like the genius in the bottle.

I'm afraid you are gonna believe that if the last formula comes out right.

 

[Fillonte]

Wow that's one hell of a formula! And it works too!

I'm even more amazed and yes, I'm afraid I believe you are the genius in the bottle now :P .

Thank you so much again for all your help.

Best wishes

 

[Aladin Akyurek]

Wow that's one hell of a formula! And it works too!

I'm even more amazed and yes, I'm afraid I believe you are the genius in the bottle now :P .

Thank you so much again for all your help.

Best wishes

You are welcome.