Tough Excel Question

[LondonBroil]

Suppose you have a spreadsheet where down the 1st column you have something like 1,1,1,1,2,2,2,2,3,3,3,3,etc. Down the 2nd column you have A,B,C,D,A,B,C,D,A,B,C,D,etc. Down the 3rd columnm you have 0.1, 0.2, 0.3, 0.4, 0.5, 0.6, etc.

Is there a way to use the VLOOKUP function to input 2 variables, 1 from column 1 and 1 from column 2, to return the corresponding number in the 3rd column?

The problem is that I can only figure out how to input 1 variable. So if I do a =VLOOKUP(2,A2:Cx,3,FALSE), then it only returns a 0.5 because that's the number in the 3rd column that corresponds to the first number 2 in the 1st column.

If I wanted to return a 0.7, I'd need to input a 2 from the 1st column, AND a C from the 2nd column.

Any ideas?

[cbfair]

jeebus, that is a tough question!

[davelin]

You could create a fourth column that is the combination of the first two and do a VLOOKUP on that column. Am I misunderstanding your question?

[Soul Daddy]

[ QUOTE ]
You could create a fourth column that is the combination of the first two and do a VLOOKUP on that column.

[/ QUOTE ]
Yes. "&" is your friend.

[TheWorstPlayer]

[ QUOTE ]
You could create a fourth column that is the combination of the first two and do a VLOOKUP on that column. Am I misunderstanding your question?

[/ QUOTE ]
This is the correct answer. If for some reason you cannot manipulate the data, then you can get funky with array functions instead. But usually, you should just create a new column in front of your data table called ID or Index or Key or whatever and just concatenate the two columns that you want to look up. Then just look up the concatenation.

[maryfield48]

[ QUOTE ]
You could create a fourth column that is the combination of the first two and do a VLOOKUP on that column. Am I misunderstanding your question?

[/ QUOTE ]

And you have to sort the data by that column, I believe.

[TheWorstPlayer]

[ QUOTE ]
[ QUOTE ]
You could create a fourth column that is the combination of the first two and do a VLOOKUP on that column. Am I misunderstanding your question?

[/ QUOTE ]

And you have to sort the data by that column, I believe.

[/ QUOTE ]
For a real vlookup, this is true. Which is why it is often preferable to use Index(DataColumn, Match(LookupValue, KeyColumn,0),false) instead of a true vlookup. The Index and Match functions are also more flexible than vlookups.

[davelin]

[ QUOTE ]
[ QUOTE ]
[ QUOTE ]
You could create a fourth column that is the combination of the first two and do a VLOOKUP on that column. Am I misunderstanding your question?

[/ QUOTE ]

And you have to sort the data by that column, I believe.

[/ QUOTE ]
For a real vlookup, this is true. Which is why it is often preferable to use Index(DataColumn, Match(LookupValue, KeyColumn,0),false) instead of a true vlookup. The Index and Match functions are also more flexible than vlookups.

[/ QUOTE ]

Interesting, although I know excel says it needs to be sorted as a default, I've never run into a problem using vlookup's on unsorted tables.

[TheWorstPlayer]

[ QUOTE ]
[ QUOTE ]
[ QUOTE ]
[ QUOTE ]
You could create a fourth column that is the combination of the first two and do a VLOOKUP on that column. Am I misunderstanding your question?

[/ QUOTE ]

And you have to sort the data by that column, I believe.

[/ QUOTE ]
For a real vlookup, this is true. Which is why it is often preferable to use Index(DataColumn, Match(LookupValue, KeyColumn,0),false) instead of a true vlookup. The Index and Match functions are also more flexible than vlookups.

[/ QUOTE ]

Interesting, although I know excel says it needs to be sorted as a default, I've never run into a problem using vlookup's on unsorted tables.

[/ QUOTE ]
I'm pretty sure that I have and can try to construct an example if you want. But I mainly use Index and Match when I want to do funkier stuff that vlookups won't easily accomodate.

[maryfield48]

[ QUOTE ]
And you have to sort the data by that column, I believe.

[/ QUOTE ]

For a real vlookup, this is true. Which is why it is often preferable to use Index(DataColumn, Match(LookupValue, KeyColumn,0),false) instead of a true vlookup. The Index and Match functions are also more flexible than vlookups.

[/ QUOTE ]

I'm so glad I came into this thread. I've learnt something that will be very useful.