Match Index with multiple columns/searches?

[ExcelNoob2008]

Sheet setup

How do you use index with multiple columns

if I do =INDEX(C2:C4,MATCH(INDEX(E6:E25,MATCH(B3,C6:C25,0)),B2:B4,0))


Then it works.. I get the name that I need.

but if I use

=INDEX(C2:C4&I2:I4&K2:K4,MATCH(INDEX(E6:E25&O6:25,MATCH(B3,C6:C25&P6:P26,0)),B2:B4&H2:H4&J2:J4,0))

So basically I have 2 columns with possible names. I need to search BOTH columns for the name and then look into the cell next to it to grab a NUMBER. Then Search 3 columns of names that will correspond ot that number and grab the name.

When I try the & with other cells it doesn't work properly. Could someone explain how to use INDEX/Match with multiple colums each section of the formula and return just one result

[ChemistB]

Can you upload an example spreadsheet (Go Advanced>Manage Attachments)? Show what you have, what you want to return.

[ExcelNoob2008]

ok here it is

on Patient Data sheet I am trying to match the nurse with the patient name. In the end there will be 30 of these sheets and I need to use DCOUNTA to count the number of different nurses that had that patients. So I am trying to grab the name on each sheet.

The index/match needs to search each column of patient name for the names and then needs to search each column number of the nurse to match the nurse.


SO as you change the nurse # it will take it from above. I can get it work if I only use the ONE range, but having it search multiple ranges produces different results, sometimes TWO of the names above.

thanks

[ExcelNoob2008]

if I use VLOOKUP(B3,'Su-N'!C6:E25&'Su-N'!O6:Q25,3,FALSE) then I get the number corresponding to the nurse and it works for BOTH columns (using CTRL+SHIFT+ENTER).

but If i combine it

=VLOOKUP(VLOOKUP(B3,'Su-N'!C6:E25&'Su-N'!O6:Q25,3,FALSE),'Su-N'!B2:E4,2,FALSE)

I get a value\na error, but if I remove the inside Vlookup and manually put 2 or 3, etc, it will look up the name?

hmmm

[ExcelNoob2008]

so is what I am asking impossible with standard functions???

[XOR LX]

Hi.

Perhaps if you re-posted your workbook with some manually-calculated expected results added and highlighted then it would be clearer what you are trying to do.

Regards

[ExcelNoob2008]

OK here is an updated template. Since I was asked to show my final result I expanded a little more on what exactly is needed.


on Patient Data Sheet there are names (used for Data List) to select what will be used on the main Sheets Su-N/Su-D/Su-E.

I am trying to count the number of different caregivers (nurses) assigned to each patient for all the sheets (assignment sheets). Each person will count as one. I need to search BOTH columns of patient names and match up with both columns of Numbers. Then match it with the numbers in the 3 columns above and their nurses. Then count number of Different names to get a total number of caregivers assigned to that patient for all sheets.

In the end there will be lots of sheets, but for now I just included SUNDAY.

Thanks in advance.

[ExcelNoob2008]

I tried naming ranges and still didn't work. I really don't want to use VBA if at all possible.

[ExcelNoob2008]

well I still can't figure it out totally... I guess this one has stumped many others as well..

[ChemistB]

Yes, I think counting unique references across multiple spreadsheets will require using VBA. Maybe you want to post in the Excel programming section.

[ExcelNoob2008]

what about just returning the ONE sheet result then? I can't seem to get it to work will all columns on the top with the nurses.. with the patients for some reason I can use this..

{=INDEX('WK1-Su-N'!Q$6:Q$25&'WK1-Su-N'!E$6:E$25,MATCH(B3,'WK1-Su-N'!O$6:O$25&'WK1-Su-N'!C$6:C$25,0),1)}

and it will return the number next to them with no issues. (so its searching BOTH colums and returning the correct result).

But if I try to use Match to search multiple columns (3 columns) for the nurses it will give me NA error.. weird..

If i can get it to find the patients name, get the assigned number next to it, then search the nurses #'s to get the name I think I can build another sheet to just list these, but I can't get all of that. I can get most, but not all.

the last function weather to use INDEX or Match, or Vlookup only wants ONE range, won't accept multiple. If someone could fix that then that would be awesome..