Index Match based on multiple colums

**Crawfy** · 08-29-2018, 01:38 AM

Thank you in advance.
Currently i use the formula below to match data from one worksheet to the next.

Please Login or Register  to view this content.

I look at the data in column AQ, if it exists on my other worksheet (Adobe Report) in column I i return the result form Adobe Report column D.

Now i have another level of complexity. If the data in the original worksheet in either colums A, AQ or AR appears in Adobe Report column I, I still want to return the result in column D.
Basically, how to i make Excel compare the data in three columns rather than one?

**FDibbins** · 08-29-2018, 01:59 AM

Im confused as to where you want this formula to be. Can you show where you want it, (again) what it is based on and what you expect?

**Crawfy** · 08-29-2018, 04:00 AM

Thank you for offering to help.

Basically i have a list of staff who have all been given one of three login names to use.
I need to check if any one of them has been used for a training application.

So...The formula is in column AY of tab "Post_Bulk_Upload" (starting at AY2)
It looks at data in columns A, AQ & AR of the same tab.
If either of these are in column I of tab "Adobe Report" it returns the corresponding data from column D of tab "Adobe Report".

It works fine now because it only looks a data from column AQ.

I need it to check if three not one possible logins is on tab "Adobe Report" and give me the training status of the user.

The columns are linked to another application and cannot be rearranged.

**devaraju** · 08-29-2018, 04:22 AM

Hi guys,

Need help in Excel 2013. I have two different columns, in which column A (Asset.CIName) has single value, wherein in the
Column B,(Related Parent Assets.CI Name) multiple matches are found for one CIName. I need those multiple values for each CI Name
in (Column RelatedCIs)other sheet.. RelatedCI.JPG
How do i return multiple values?

CIlookup.JPG

**Crawfy** · 08-29-2018, 06:28 AM

@devaraju your post isn't related to this post, please kindly remove and open your own thread.
Thank you.

**FDibbins** · 08-29-2018, 02:11 PM

@ devaraju (perhaps this has happened to you before?)
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

**FDibbins** · 08-29-2018, 02:29 PM

@ Crawfy, you have a mixture of text "numbers" and real numbers in both sheets. If you look in Post col A, you will see a small green triangle in the top left of each cell - Click the cell and a small yellow diamond appears. Read what it says.
It is not a problem if all in 1 sheet are text numbers, and all on the other are real numbers, but having them mixed up, like on Adobe, causes unnecessary problems.

If you can fix that, try 1 of these...
=INDEX('Adobe Report'!$D$2:$D$10000,IFERROR(IFERROR(MATCH(--$A2,'Adobe Report'!$I$2:$I$10000,0),MATCH($AR2,'Adobe Report'!$I$2:$I$10000,0)),MATCH($AQ2,'Adobe Report'!$I$2:$I$10000,0)))
if ALL numbers in Adobe col I are all real numbers (see I4 and I5)

or this if both sheets contain text numbers...
=INDEX('Adobe Report'!$D$2:$D$10000,IFERROR(IFERROR(MATCH($A2,'Adobe Report'!$I$2:$I$10000,0),MATCH($AR2,'Adobe Report'!$I$2:$I$10000,0)),MATCH($AQ2,'Adobe Report'!$I$2:$I$10000,0)))

**Crawfy** · 08-29-2018, 03:54 PM

Thank you for to advice. I’ll refine the data and try your options.

**FDibbins** · 08-30-2018, 01:33 AM

Happy to help and thanks for the feedback

Please let me know how you make out?

Index Match based on multiple colums

LinkBack

Thread Tools

Rate This Thread

Display

Index Match based on multiple colums

Re: Index Match based on multiple colums

Re: Index Match based on multiple colums

Re: Index Match based on multiple colums

Re: Index Match based on multiple colums

Re: Index Match based on multiple colums

Re: Index Match based on multiple colums

Re: Index Match based on multiple colums

Re: Index Match based on multiple colums

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to use Index and Match based on multiple criteria using multiple worksheets

[SOLVED] Index-Match based on multiple criteria

[SOLVED] Index/Match over multiple colums

[SOLVED] Use INDEX MATCH to return data between dates and get AVERAGE of values in two colums

index match based on multiple criterias

Index and Match Based on Multiple Criteria

Index Match Multiple Colums Headers

Bookmarks

Bookmarks

Posting Permissions