what am i not doing correctly ?
I want to be able to search in a list for a matching employee number and provide the name of his secretary.
thanks
what am i not doing correctly ?
I want to be able to search in a list for a matching employee number and provide the name of his secretary.
thanks
VLOOKUP can lookup values to the left so, try this one instead
Formula:=INDEX(Sheet2!C$3:C$5,MATCH(B3,Sheet2!D$3:D$5,0))
Last edited by AlKey; 02-12-2016 at 09:37 AM.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
You need to have the director Number first on Sheet 2, since that is what you are searching by. Formula would be =VLOOKUP(B3,Sheet2!B:C,1,0)
You need to reverse your data in Sheet2: VLOOKUP uses the first range as the MATCH criteria so needs to be Director is in Column C and Assistant in D
i attached the actual file i am working with but changed the names.
can you tell me what is wrong and perhaps give me 1 good example in a replied copy?
I should be good from there,
Thanks
Based on your sample file in post #9...
This formula entered in M2 and copied down:
=INDEX(Sheet4!C:C,MATCH(B2,Sheet4!F:F,0))
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
VLOOKUP requires that the lookup value (11111) be in the left-most column of the lookup table.
Instead of this...
Data Range
C D 2 Assistance Name Director # 3 Sarah 22222 4 Julie 33333 5 Samatha 11111
You need this...
Data Range
C D 2 Director # Assistance Name 3 22222 Sarah 4 33333 Julie 5 11111 Samatha
none of the above work
can someone provide me with a bit more details
thanks
this is my reasoning;
=VLOOKUP(A1,Sheet2!$A$1:$B$100,2,FALSE)
mean
search value in A1 on sheet2 between A1 and A100.
If you find a match, provide me with the second column and false means I'm looking for an exact match.
Is that correct ?
Once I "flipped" the lookup table around it works for me...
Data Range
C D 2 Director # Assistance Name 3 22222 Sarah 4 33333 Julie 5 11111 Samatha
Data Range
B C D 2 Director # Director Name Secretary Name 3 11111 John Samatha 4 22222 Peter Sarah 5 33333 Sam Julie
This formula entered in D3 and copied down:
=VLOOKUP(B3,Sheet2!C$3:D$5,2,0)
In M2-
Instead of Vlookup Index Match should be used here.=INDEX(Sheet4!C:C,MATCH(B2,Sheet4!F:F,0))
This is because Vlookup searches the first column in the range specified but index match is more flexible and can search columns from between the data also.
Happy to Help
How to upload excel workbooks at this forum - http://www.excelforum.com/the-water-...his-forum.html
"I don't get things easily, so please be precise and elaborate"
If someone's post has helped you, thank by clicking on "Add Reputation" below the post.
If your query is resolved please mark the thread as "Solved" from the "Thread Tools" above.
Sourabh
As previously explained you need Consultant in Column A in Sheet4 using VLOOKUP
then use the following
=IFERROR(VLOOKUP(B2,Sheet4!$A$2:$F$4,4,FALSE),"")
Enter in M2 and copy down
Formula:=IFERROR(INDEX(Sheet4!C$2:C$5,MATCH(B3,Sheet4!$F$2:$F$5,0)),"Not found")
everything works, thank you!!
Good deal. Thanks for the feedback!
If your question has been solved please mark the thread as being solved.
In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.
I have one last question;
When i query to find the secretary, some consultant have more than one.
Is the a way I can make it so that when there's more than 1 result for same consultant, it posts it on a seperate lines to the right ?
Thanks
Like this...
Data Range
A B C D E F G H 1 Secretary Consultant Consultant Secretary 2 Judy 111 111 Judy Bill Sandy 3 Tina 222 222 Tina 4 Linda 333 333 Linda Eric 5 Bill 111 444 Connie Tracy 6 Connie 444 555 Tom 7 Tracy 444 8 Tom 555 9 Eric 333 10 Sandy 111 11 ------ ------ ------ ------ ------ ------ ------ ------
This array formula** entered in E2:
=IFERROR(INDEX($A:$A,SMALL(IF($B$2:$B$10=$D2,ROW(B$2:B$10)),COLUMNS($E2:E2))),"")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Copy down as needed then across until you get a column full of blanks.
Here's a sample file...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks