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.

[icepick]

The key to your problem is to not arrange things into three colums, but do do an array like. Your first column would be the same, but your second, instead of a column would be a row.

IE, instead of this:

1 A x
2 B x
3 C x
4 D x
5 E x

You'd have:
A B C D E
1 x x x x x
2 x x x x x
3 x x x x x
4 x x x x x
5 x x x x x

Forumla: =VLOOKUP(row#key, $table$table, MATCH(columnkey, $header$row:$range$x, 0), FALSE)

http://www.techonthenet.com/excel/formulas/2d_lookup.php

[TheWorstPlayer]

That's a)a pain in the ass and b)going to mess up pivot tables that need it in the first format.

[FatOtt]

Putting things in alphabetic columns like Excel does, you've got numbers in column A and letters in column B. Insert a column C that merges these two, so that cell C1=a1&b1. Then do your vlookup in column D on the value you desire from column C.

To create your lookup value, do the same thing, maybe in a row above the dataset. So insert 2 rows above all of your data. In cell A1, you will input the value from column A that you want. In cell B1, you will input the value from column B that you want. Cell C1 will = A1&B1. Your lookup formula will then reference C1. Or you can type the letter/number combination directly into the vlookup formula like you did in your example.

Edit: obviously not fast enough

[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.

[LondonBroil]

[ 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 ]

My sister is having this problem. The first column is actually tubing outside diamter (e.g. 0.25 inches) and the 2nd column isn't letters, it's tubing guage (e.g. "20" guage). I was not aware of this before since she just sent me a "basic" version of the table that had letters instead of numbers in column 2. So if we made a 4th column and combined them, it would look like 0.2520 which doesn't make sense.

She wants the values in column A and B to be user inputed and have it spit out the corresponding C column value.

And to the other person who mentioned having the 2nd column as a row. She mentioned she COULD do it that way, but she wants to keep it set up the way it is.

[davelin]

Could you still create your 4th column, create a cell which is the combination of two user-inputed numbers, and the do a lookup on that cell?

[maryfield48]

[ 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 ]

My sister is having this problem. The first column is actualy tubing outside diamter (e.g. 0.25 inches) and the 2nd column isn't letters, it's tubing guage (e.g. "20" guage). So if we made a 4th column and combined them, it would look like 0.2520 which doesn't make sense.

She wants the values in column A and B to be user inputed and have it spit out the corresponding C column value.

This is a real pain.

[/ QUOTE ]

It doesn't have to make sense, it will still work. Let's say you add a column that concatenates the contents of cell A2 [i.e. .25] and the contents of cell B2 [i.e. 20] as ".25/20" [The formula would read =concatenate(a2&"/"&b2)]

You could have the user input the value they need [.25/20] into another cell, say G2, and in G3 you have the formula =vlookup(G2,table_array,4,FALSE) to return the required value. The user would just have to know the correct syntax, i.e. outside diameter/guage.

[TheWorstPlayer]

Same as before. Just to make sure that 2&232 looks different from 22&32, add an underscore or something so your formula will look like =a1&"_"&b1 for your index column.

[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.

[Patrick del Poker Grande]

Man... just use Matlab instead.

[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.

[TheWorstPlayer]

[ QUOTE ]
[ 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.

[/ QUOTE ]
At work, I get paid for giving Excel training. /images/graemlins/tongue.gif

[sfer]

[ QUOTE ]
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?

[/ QUOTE ]

Concatenate the columns into a long string and vlookup that.

[SuitedSixes]

I get more turned on by a good Excel thread than I do by anything by Larence Ng.

[TheWorstPlayer]

[ QUOTE ]
Concatenate the columns into a long string and vlookup that.

[/ QUOTE ]
I also love replying to 20 reply threads with comments that have been made 5 times.

[TheWorstPlayer]

[ QUOTE ]
I get more turned on by a good Excel thread than I do by anything by Larence Ng.

[/ QUOTE ]
Any thread with your avatar in it counts as a good thread in my book.

[sfer]

[ QUOTE ]
[ QUOTE ]
Concatenate the columns into a long string and vlookup that.

[/ QUOTE ]
I also love replying to 20 reply threads with comments that have been made 5 times.

[/ QUOTE ]

Ban me.

[LondonBroil]

[ 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 ]

My sister is having this problem. The first column is actualy tubing outside diamter (e.g. 0.25 inches) and the 2nd column isn't letters, it's tubing guage (e.g. "20" guage). So if we made a 4th column and combined them, it would look like 0.2520 which doesn't make sense.

She wants the values in column A and B to be user inputed and have it spit out the corresponding C column value.

This is a real pain.

[/ QUOTE ]

It doesn't have to make sense, it will still work. Let's say you add a column that concatenates the contents of cell A2 [i.e. .25] and the contents of cell B2 [i.e. 20] as ".25/20" [The formula would read =concatenate(a2&"/"&b2)]

You could have the user input the value they need [.25/20] into another cell, say G2, and in G3 you have the formula =vlookup(G2,table_array,4,FALSE) to return the required value. The user would just have to know the correct syntax, i.e. outside diameter/guage.

[/ QUOTE ]

This is what I sent her. She said she'll use this unless she could find a better version. She really wanted the user to have to enter "0.25" in 1 box and "20" in another box then have it return the value but entering 0.25/20 is almost just as good, as long as the user inputs the values in the right format.

Thanks for everyone's help!

[ QUOTE ]
Man... just use Matlab instead.

[/ QUOTE ]

Exactly. The amount of effort people put into creating complicated excel stuff blows my mind when there is much easier solutions out there.

[davelin]

[ QUOTE ]
She really wanted the user to have to enter "0.25" in 1 box and "20" in another box then have it return the value but entering 0.25/20 is almost just as good

[/ QUOTE ]

You can easily do this. Make two user-input cells like E1 and F1. Have hidden a third cell like G1 that concatenates these two cells. Do the vlookup on this cell. Voila.

[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 ]

My sister is having this problem. The first column is actualy tubing outside diamter (e.g. 0.25 inches) and the 2nd column isn't letters, it's tubing guage (e.g. "20" guage). So if we made a 4th column and combined them, it would look like 0.2520 which doesn't make sense.

She wants the values in column A and B to be user inputed and have it spit out the corresponding C column value.

This is a real pain.

[/ QUOTE ]

It doesn't have to make sense, it will still work. Let's say you add a column that concatenates the contents of cell A2 [i.e. .25] and the contents of cell B2 [i.e. 20] as ".25/20" [The formula would read =concatenate(a2&"/"&b2)]

You could have the user input the value they need [.25/20] into another cell, say G2, and in G3 you have the formula =vlookup(G2,table_array,4,FALSE) to return the required value. The user would just have to know the correct syntax, i.e. outside diameter/guage.

[/ QUOTE ]

This is what I sent her. She said she'll use this unless she could find a better version. She really wanted the user to have to enter "0.25" in 1 box and "20" in another box then have it return the value but entering 0.25/20 is almost just as good, as long as the user inputs the values in the right format.

Thanks for everyone's help!

[/ QUOTE ]
What she wants to do is extremely simple. Just have them type into CellA, say, .25 and CellB, say .4. Then use =VLOOKUP(CellA&"_"&CellB,Array,Column#,FALSE) if you used my underscore method for your KeyColumn.

[TheWorstPlayer]

[ QUOTE ]
[ QUOTE ]
She really wanted the user to have to enter "0.25" in 1 box and "20" in another box then have it return the value but entering 0.25/20 is almost just as good

[/ QUOTE ]

You can easily do this. Make two user-input cells like E1 and F1. Have hidden a third cell like G1 that concatenates these two cells. Do the vlookup on this cell. Voila.

[/ QUOTE ]
You can do the concatenation right in the vlookup.

[davelin]

[ QUOTE ]
[ QUOTE ]
[ QUOTE ]
She really wanted the user to have to enter "0.25" in 1 box and "20" in another box then have it return the value but entering 0.25/20 is almost just as good

[/ QUOTE ]

You can easily do this. Make two user-input cells like E1 and F1. Have hidden a third cell like G1 that concatenates these two cells. Do the vlookup on this cell. Voila.

[/ QUOTE ]
You can do the concatenation right in the vlookup.

[/ QUOTE ]

Good to know.

[dabluebery]

The right answer is to use the index function, it's the "right" way to do it. Make this post over at the Mrexcel.com forums, those people will give you a spreadsheet, documentation, commented notes, and the history of the index function in 10 minutes.