Home |
Search |
Today's Posts |
#1
|
|||
|
|||
posting values
on excel,i've been using v-lookup to get values in the same row from one
column but i was wondering if it was possible to use data in 2 columns and get a value in the same row to transfer into my data sheet. I looked at every function in the program but can't seem to find the way to do that. if anyone knows how to do this, pls respond to this. thanks-lisa |
#2
|
|||
|
|||
Lisa,
VLOOKUP can match a value in the first column and return the value from any other column - and the same row as the matched value - just expand the second range to include the data that you want to have returned, and use the third parameter to specify the column number. For example, to match a value in column A and return the value from column C, along the lines of =VLOOKUP("Lisa",A1:C10,3,FALSE) The looked-up value can also be a cell reference: =VLOOKUP(F1,A1:C10,3,FALSE) -- HTH, Bernie MS Excel MVP "lisa" wrote in message ... on excel,i've been using v-lookup to get values in the same row from one column but i was wondering if it was possible to use data in 2 columns and get a value in the same row to transfer into my data sheet. I looked at every function in the program but can't seem to find the way to do that. if anyone knows how to do this, pls respond to this. thanks-lisa |
#3
|
|||
|
|||
it's still not clear to me how to do it. If i have values in columns A,B and
C in my reference table and need to match up the same values in the same row in column A and B to return the value of column C in my data then how does that work? for example: Reference table column A|Column B| Column C blue red purple red yellow orange black yellow brown data table column A|Column B| Column C red green red yellow blue yellow SO i want column A and B in the data table to match up so that it gives the correct C value from the reference table. can anyone further explain how to use the V-look up for that? "Bernie Deitrick" wrote: Lisa, VLOOKUP can match a value in the first column and return the value from any other column - and the same row as the matched value - just expand the second range to include the data that you want to have returned, and use the third parameter to specify the column number. For example, to match a value in column A and return the value from column C, along the lines of =VLOOKUP("Lisa",A1:C10,3,FALSE) The looked-up value can also be a cell reference: =VLOOKUP(F1,A1:C10,3,FALSE) -- HTH, Bernie MS Excel MVP "lisa" wrote in message ... on excel,i've been using v-lookup to get values in the same row from one column but i was wondering if it was possible to use data in 2 columns and get a value in the same row to transfer into my data sheet. I looked at every function in the program but can't seem to find the way to do that. if anyone knows how to do this, pls respond to this. thanks-lisa |
#4
|
|||
|
|||
I'm sorry. When you said "use data in two columns" I did not think you meant
match data in each of two columns. That requires something different: and array formula, entered using Ctrl-Shift-Enter =INDIRECT("C"&MAX(($A$1:$A$3=A7)*($B$1:$B$3=B7)*(R OW($B$1:$B$3)))) where your reference table is in A1:C3, and your key lookup values are in A7 and B7, and the formula is in C7. It will return an error if the combination in A7 and B7 doesn't appear in your reference table. To use VLOOKUP, you would need to create an new column A, with the formula =A1&B1 Copied down to match all your reference and lookup data, and then use =VLOOKUP(A7,$A$1:$D$3,4,FALSE) HTH, Bernie MS Excel MVP "lisa" wrote in message ... it's still not clear to me how to do it. If i have values in columns A,B and C in my reference table and need to match up the same values in the same row in column A and B to return the value of column C in my data then how does that work? for example: Reference table column A|Column B| Column C blue red purple red yellow orange black yellow brown data table column A|Column B| Column C red green red yellow blue yellow SO i want column A and B in the data table to match up so that it gives the correct C value from the reference table. can anyone further explain how to use the V-look up for that? "Bernie Deitrick" wrote: Lisa, VLOOKUP can match a value in the first column and return the value from any other column - and the same row as the matched value - just expand the second range to include the data that you want to have returned, and use the third parameter to specify the column number. For example, to match a value in column A and return the value from column C, along the lines of =VLOOKUP("Lisa",A1:C10,3,FALSE) The looked-up value can also be a cell reference: =VLOOKUP(F1,A1:C10,3,FALSE) -- HTH, Bernie MS Excel MVP "lisa" wrote in message ... on excel,i've been using v-lookup to get values in the same row from one column but i was wondering if it was possible to use data in 2 columns and get a value in the same row to transfer into my data sheet. I looked at every function in the program but can't seem to find the way to do that. if anyone knows how to do this, pls respond to this. thanks-lisa |
#5
|
|||
|
|||
Hi bernie,
i'm sorry, i'm still not getting it. I think i need to know how to do this step by step. Does it make it easier if i said that the reference table is on a separate worksheet? I'm not sure if I am suppose to put the formula when i use the vlookup on the 3rd column or in a new column A that you were mentioning.. so confused... "Bernie Deitrick" wrote: I'm sorry. When you said "use data in two columns" I did not think you meant match data in each of two columns. That requires something different: and array formula, entered using Ctrl-Shift-Enter =INDIRECT("C"&MAX(($A$1:$A$3=A7)*($B$1:$B$3=B7)*(R OW($B$1:$B$3)))) where your reference table is in A1:C3, and your key lookup values are in A7 and B7, and the formula is in C7. It will return an error if the combination in A7 and B7 doesn't appear in your reference table. To use VLOOKUP, you would need to create an new column A, with the formula =A1&B1 Copied down to match all your reference and lookup data, and then use =VLOOKUP(A7,$A$1:$D$3,4,FALSE) HTH, Bernie MS Excel MVP "lisa" wrote in message ... it's still not clear to me how to do it. If i have values in columns A,B and C in my reference table and need to match up the same values in the same row in column A and B to return the value of column C in my data then how does that work? for example: Reference table column A|Column B| Column C blue red purple red yellow orange black yellow brown data table column A|Column B| Column C red green red yellow blue yellow SO i want column A and B in the data table to match up so that it gives the correct C value from the reference table. can anyone further explain how to use the V-look up for that? "Bernie Deitrick" wrote: Lisa, VLOOKUP can match a value in the first column and return the value from any other column - and the same row as the matched value - just expand the second range to include the data that you want to have returned, and use the third parameter to specify the column number. For example, to match a value in column A and return the value from column C, along the lines of =VLOOKUP("Lisa",A1:C10,3,FALSE) The looked-up value can also be a cell reference: =VLOOKUP(F1,A1:C10,3,FALSE) -- HTH, Bernie MS Excel MVP "lisa" wrote in message ... on excel,i've been using v-lookup to get values in the same row from one column but i was wondering if it was possible to use data in 2 columns and get a value in the same row to transfer into my data sheet. I looked at every function in the program but can't seem to find the way to do that. if anyone knows how to do this, pls respond to this. thanks-lisa |
#6
|
|||
|
|||
Lisa,
Contact me privately and I will send you a working version. HTH, Bernie MS Excel MVP "lisa" wrote in message ... Hi bernie, i'm sorry, i'm still not getting it. I think i need to know how to do this step by step. Does it make it easier if i said that the reference table is on a separate worksheet? I'm not sure if I am suppose to put the formula when i use the vlookup on the 3rd column or in a new column A that you were mentioning.. so confused... "Bernie Deitrick" wrote: I'm sorry. When you said "use data in two columns" I did not think you meant match data in each of two columns. That requires something different: and array formula, entered using Ctrl-Shift-Enter =INDIRECT("C"&MAX(($A$1:$A$3=A7)*($B$1:$B$3=B7)*(R OW($B$1:$B$3)))) where your reference table is in A1:C3, and your key lookup values are in A7 and B7, and the formula is in C7. It will return an error if the combination in A7 and B7 doesn't appear in your reference table. To use VLOOKUP, you would need to create an new column A, with the formula =A1&B1 Copied down to match all your reference and lookup data, and then use =VLOOKUP(A7,$A$1:$D$3,4,FALSE) HTH, Bernie MS Excel MVP "lisa" wrote in message ... it's still not clear to me how to do it. If i have values in columns A,B and C in my reference table and need to match up the same values in the same row in column A and B to return the value of column C in my data then how does that work? for example: Reference table column A|Column B| Column C blue red purple red yellow orange black yellow brown data table column A|Column B| Column C red green red yellow blue yellow SO i want column A and B in the data table to match up so that it gives the correct C value from the reference table. can anyone further explain how to use the V-look up for that? "Bernie Deitrick" wrote: Lisa, VLOOKUP can match a value in the first column and return the value from any other column - and the same row as the matched value - just expand the second range to include the data that you want to have returned, and use the third parameter to specify the column number. For example, to match a value in column A and return the value from column C, along the lines of =VLOOKUP("Lisa",A1:C10,3,FALSE) The looked-up value can also be a cell reference: =VLOOKUP(F1,A1:C10,3,FALSE) -- HTH, Bernie MS Excel MVP "lisa" wrote in message ... on excel,i've been using v-lookup to get values in the same row from one column but i was wondering if it was possible to use data in 2 columns and get a value in the same row to transfer into my data sheet. I looked at every function in the program but can't seem to find the way to do that. if anyone knows how to do this, pls respond to this. thanks-lisa |
#7
|
|||
|
|||
|
#8
|
|||
|
|||
Lisa,
Remove the "dot" and you've got it. HTH, Bernie MS Excel MVP "lisa" wrote in message ... what's your email address? ? "Bernie Deitrick" wrote: Lisa, Contact me privately and I will send you a working version. HTH, Bernie MS Excel MVP "lisa" wrote in message ... Hi bernie, i'm sorry, i'm still not getting it. I think i need to know how to do this step by step. Does it make it easier if i said that the reference table is on a separate worksheet? I'm not sure if I am suppose to put the formula when i use the vlookup on the 3rd column or in a new column A that you were mentioning.. so confused... "Bernie Deitrick" wrote: I'm sorry. When you said "use data in two columns" I did not think you meant match data in each of two columns. That requires something different: and array formula, entered using Ctrl-Shift-Enter =INDIRECT("C"&MAX(($A$1:$A$3=A7)*($B$1:$B$3=B7)*(R OW($B$1:$B$3)))) where your reference table is in A1:C3, and your key lookup values are in A7 and B7, and the formula is in C7. It will return an error if the combination in A7 and B7 doesn't appear in your reference table. To use VLOOKUP, you would need to create an new column A, with the formula =A1&B1 Copied down to match all your reference and lookup data, and then use =VLOOKUP(A7,$A$1:$D$3,4,FALSE) HTH, Bernie MS Excel MVP "lisa" wrote in message ... it's still not clear to me how to do it. If i have values in columns A,B and C in my reference table and need to match up the same values in the same row in column A and B to return the value of column C in my data then how does that work? for example: Reference table column A|Column B| Column C blue red purple red yellow orange black yellow brown data table column A|Column B| Column C red green red yellow blue yellow SO i want column A and B in the data table to match up so that it gives the correct C value from the reference table. can anyone further explain how to use the V-look up for that? "Bernie Deitrick" wrote: Lisa, VLOOKUP can match a value in the first column and return the value from any other column - and the same row as the matched value - just expand the second range to include the data that you want to have returned, and use the third parameter to specify the column number. For example, to match a value in column A and return the value from column C, along the lines of =VLOOKUP("Lisa",A1:C10,3,FALSE) The looked-up value can also be a cell reference: =VLOOKUP(F1,A1:C10,3,FALSE) -- HTH, Bernie MS Excel MVP "lisa" wrote in message ... on excel,i've been using v-lookup to get values in the same row from one column but i was wondering if it was possible to use data in 2 columns and get a value in the same row to transfer into my data sheet. I looked at every function in the program but can't seem to find the way to do that. if anyone knows how to do this, pls respond to this. thanks-lisa |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Zero values in a log chart | Charts and Charting in Excel | |||
how do you prevent data from changing values when sorting linked . | Excel Discussion (Misc queries) | |||
How sum values in column B using values in column A as the conditi | Excel Worksheet Functions | |||
Second serie doesn't use X-as values | Charts and Charting in Excel | |||
Formula to list unique values | Excel Worksheet Functions |