Index(?)

Topic

New Reply

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.

Any help? Thanks in advance.

Reply | Quote

Replies

JL,

This should do the trick:
[noparse]=INDEX(Sheet2!A1:B10,MATCH($A1,Sheet2!A1:A10,0),2)[/noparse]
Place the above formula in cell C1 of Sheet1 and then fill down.
36544-indexmatch
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 :cheers:
36545-JLKIRK-IndexMatch

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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)

Reply | Quote

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. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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! :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote