1. ## Index(?)

Column A, Rows 1-10, Worksheet 1 are the letters A-J. Column A, Rows 1-10, Worksheet 2 are the letters A-J, Column B, Rows 1-10, names of clients. What I would like to do on Worksheet 1, column C, is "match" the client name on Worksheet 2 with the associated letters on Worksheet 1. For example, Column A, Row 1, Worksheet 1 contains A. Column A, Row 1, Worksheet 2 contains an A as well, and Column B, Row 1 contains the name "John". What I would like to do is insert a formula in Worksheet 1, Column C, Row 1 that matches the two As, and would result in the name "John" being inserted.

2. JL,

This should do the trick:
=INDEX(Sheet2!A1:B10,MATCH(\$A1,Sheet2!A1:A10,0),2)
Place the above formula in cell C1 of Sheet1 and then fill down.
indexmatch.JPG
Of course you can improve the formula by naming the range Sheet2!A1:A10 and Sheet2!A1:B10 and using the names in the formula.
HTH
JLKIRK-IndexMatch.xlsx

3. A shorter version of the index/match is to index on only 1 column:
=INDEX('Worksheet 2'!\$B\$1:\$B\$10,MATCH(A1,'Worksheet 2'!\$A\$1:\$A\$10,0))

But in this case the more direct way is to use a VLOOKUP:
=VLOOKUP(A1,'Worksheet 2'!\$A\$1:\$B\$10,2,0)

Steve
PS: RG, I think locking the cell ranges is preferred in this instance otherwise if the order is changed you may not find a match (if the appropriate match is on a row < low your lookup value is on)

4. Originally Posted by sdckapr
PS: RG, I think locking the cell ranges is preferred in this instance otherwise if the order is changed you may not find a match (if the appropriate match is on a row < low your lookup value is on)
Exactly! That's why I suggested using Named Ranges.

BTW: I didn't suggest VLookup because the OP didn't specify that the key letters would always be in alpha order.

5. Exactly! That's why I suggested using Named Ranges.
But even without named ranges, you can lock in the col and rows of the range so they range will not vary when the formula is copied. Your formula uses a different range for each row and if the value being looked is in a row > the row it is occupied in the table, it will not find the match...[for example if you the letter "A" in cell A2 of Sheet1, a match will not be found since it looks in Sheet2!A2:A11 so it misses the "A" in cell Sheet2!A1]

I didn't suggest VLookup because the OP didn't specify that the key letters would always be in alpha order.
I did not presume that they would be. Vlookup does not require them to be in Alphabetical order. Just like MATCH it can be used for an exact lookup with the optional parameter of zero ("0") at the end.

Steve

6. Steve,

You're correct the search ranges should be locked down. I was in a hurry to get out the door to pick up my wife's aunt at the airport an hour and a half away and wasn't thinking that filling down would change the references. Thanks for the reminder.

7. Hi RG

What speed were you doing to pick up your wife's aunt? Did you get there on time? Did you have to carry the luggage? How's your back? And let's get our priorities right here. You had a compelling choice between answering an Excel question, or obeying the boss.

I'm still in hospital. Day 10 I think. It must be the morphine.

zeddy

8. Zeddy.

In order:
60-70 MPH
Yes
Yes
Great!!!!!!
No choice! If Momma ain't happy Nobody's happy!
Got any extra Morphine?

Get well SOON!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•