• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index/Match-Return Results Based on Two Lookup Criteria in One Cell

C

cmkarnes

Member
Hello, I am perplexed. I have a large Excel table based on data imported from Access through a MS Query. That is working fine - however, one column of data filters in showing Employee ID #s. In some cases, there are two employee numbers separated by a semicolon (i.e., 4;7). It is supposed to be like that. I added a column which uses Index/Match to return the names based on ID #. It works fine for the single number items, but I'm not sure how to modify the formula to return results for the two number items.

I am trying to stay away from Vlookup as it was wrought with issues and Index/Match is proving to be more reliable in this situation.

An example of my array table - the last row indicates how I would like results returned with two numbers:

Emp ID # Emp Name
1 EXAMPLE NAME 1
2 EXAMPLE NAME 2
3 EXAMPLE NAME 3
2;3 EXAMPLE NAME 2/EXAMPLE NAME 3

My formula currently looks like this:
=INDEX(EMPLOYEE_NAME,MATCH(LOOKUP_NUMBER,EMP_ID_NUMBER,0))

Could someone explain to me how to modify this? It would be so greatly appreciated. Thank you - Chris
 
Hi, the reason I couldn't upload workbook was due to the sensitive nature of the data and it is work-related. I will start working on a mock-up worksheet right now and will upload. Thanks!
 
Hello, I've attached a mock workbook. It is extremely stripped down - the real item has many more columns, etc., but I should be able to follow the pattern of a resolution you propose and apply to the actual workbook. As you can tell by looking at the Emp ID column in the main table, it looks like the single numbers filter in as numbers and the two numbers come in as text which I think may be the reason Vlookup didn't work for me. I have no control how the Access database I filter in from was designed so that is out of my control. The Index/Match function overcame this. Thank you so much for your assistance! Chris
 

Attachments

Wow! - this is impressive - no wonder I could not figure this out!!!!! I cannot thank you enough for the time you took to help me on this. I assume I can just hide the results columns (i.e., Column1, First ID, etc.) in the actual table? Also, do you mind explaining to me what the inside brackets signify? just asking so I understand the structure of the formula in case I am working on another project and need to use this again. Again, greatly appreciated!!!!!!!! Chris
 
Hello, I am sorry to trouble you again. I transferred all the formulas from the sample worksheet above into the actual table here at work, and everything looks correct, but I'm having issues with N/A error. It shows up where employee names populate. The Lookup employee ID #s that have more than one number in the cell populate correctly, but the cells that contain only a single number give the error. If I do the test of "1" copy/paste/special - Multiply, and highlight the employee ID # column, that takes care of the problem & everything populates correctly, but when the data refreshes itself, it knocks everything out, I get the N/A error again, and only the items with more than one number populate as they should. I've been researching the past several hours and can't seem to come up with a solution. I did not know if there's anything within the formulas that should be added/modified to correct this? thx much - chris
 
OK - but I have to strip it down due to the real data on it. The N/A error only happens though when the MS Query data refreshes - it makes me think it has to do with something in the formatting that excel is pulling in. It may take me just a bit to redo the sheet. Will send shortly. thx
 
Hi, I've atch'd stripped down item. In column A - that's just the record # from Access and not related to what we're doing here. What's interesting, on the actual table at work, I have another column(which I can't include in this sample) that populates with single numbers only, and have an index/match array table for that (not names-descriptions of certain things) and that is rock solid with no issues at all. So when the refresh occurs, the numbers move over to the left like you see in the atch'd, and when multiplied by 1 the single #s move to the right and all names populate. Thank you. c hrs
 

Attachments

Hi ,

I do not understand the real problem here ; it is true that the numbers in the First ID , Second ID columns are left aligned , but other than that I do not understand what the problem is.

The data in the Desk Officer column ( column B ) is supposed to be a text string , since it can contain a semi-colon , but this should not make any difference ; when there is only one value in this column , then it may be a numeric item , but even then it should not make any difference.

If you see two single values , as in rows 2 , 3 , 4 and 5 , you can use the TYPE function to check ; the values in column B are of type string , while the values in the First ID column ( column E ) are numbers. You can now go to any particular value in column B , and press F2 and then the ENTER key ; this will convert the string value to a numeric value. Nothing should change as far as the value in the First ID column is concerned.

Narayan
 
Hi, I figured it out - Where the formula is =IF([@Column1]=0, if double-quotes are added around the zero ("0") that takes care of the problem as it looks for text and/or numbers. This is for the formula that is located in the column that actually populates the names. Thanks again - Chris
 
Hi ,

I do not understand anything ; the column labelled Column1 can never have text in it ; the formula in this column is a subtraction of two text lengths ; it is an arithmetic operation and can never result in a text string.

=IF([@Column1]=0

is the correct way , but if any other way works for you , that's fine with me.

Narayan
 
You must log in or register to reply here.
Back
Top