0

I asked this yesterday, but I've cleared up some data this time. I have the following abridged sheet:

Sheet1
         H                        AP                AO
1 Transaction Description    Employee Name         Type
2 ER 12345678                    blank             blank
3 ER 13182984                    blank             blank
4 ER 18213289                    blank             blank
5 ER 13829429                    blank             blank
6 ER 89234024                    blank             blank

And a different abridged worksheet in the same file to reference the names against:

Sheet2
       E                           I
1 Employee Name              Expense Report Number    
2 Chris Rock                    12345678        
3 Hank Hill                     13182984          
4 Tom Sawyer                    18213289         
5 Elon Musk                     13829429          
6 Tupac Shakur                  89234024

And I was wondering how to efficiently fill in the first excel sheet's Employee Name and Type columns from the matching report number of the second sheet as such:

Sheet1
        H                            AP              AO
1 Transaction Description      Employee Name        Type   
2 ER 12345678                    Chris Rock           A
3 ER 13182984                    Hank Hill            A
4 ER 18213289                    Tom Sawyer           A
5 ER 13829429                    Elon Musk            A
6 ER 89234024                    Tupac Shakur         A

My attempt so far:

=VLOOKUP(MID(H2,4,8)+0,'EEM BI + mapping'!E$2:I$1000,6,0)

However I'm not sure if VLOOKUP will work because the result I am trying to return is to the left of the column I am trying to match. Any help is much appreciated

Improve this question
3
  • Are your Transaction Description always in the format with @@ ########, if not then you will need to rethink your approach. Commented Aug 8, 2017 at 13:53
  • @ScottCraner yes, it will always be in that format Commented Aug 8, 2017 at 13:58
  • Then check your data, make sure there are no spaces and or other non printable characters in the data. Use LEN() to count the characters and make sure they are what they should be. Commented Aug 8, 2017 at 13:59

2 Answers 2

Reset to default
4

I'm not sure if VLOOKUP will work because the result I am trying to return is to the left of the column I am trying to match

You are correct, VLOOKUP wont work in this case and you need to use INDEX/MATCH instead:

=INDEX('EEM BI + mapping'!E:E, MATCH(MID(H2,4,8)+0,'EEM BI + mapping'!I:I,0))
Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

Thanks for your reply @A.S.H! feeling a little glad that we reached the same solution, however it's returning a N/A value... Any guesses as to why that is?
Reading through Index documentation, a potential error may be that my Index array does not match my Match array length?
@codeninja if there's no match #N/A is returned. I am not sure why there's but it's probably because of comparing numbers to strings, so I kept the +0 suspecting you have a good reason for it. Did you try the suggestion of Scott Craner?
@codeninja in your debugging process, it might be useful to try a simple Match formula to see if it gets a result. For example =MATCH(13182984,'EEM BI + mapping'!I:I,0) and check if it gets you the correct row number?
@codeninja so my guess is that some of your Expense Report are numbers and some are text, That needs to be unified, to the same for the match to work properly. If you go with text then remove the +0 from the Match, If you go with numbers leave it in. Also you will get #N/A if no match is found.
|
1 
=INDEX(Sheet2!E:E,MATCH(VALUE(MID(H2,4,8))+0,Sheet2!I:I,0))

It seems to work flawlessly for me.

enter image description here

Improve this answer

1 Comment

thanks for your response, but unfortunately I'm getting a N/A error.. I suspect it may be due to an index and match length mismatch?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.