VLOOKUP

[thesproing]

Hey guys,

Can anyone tell me if you can use VLOOKUP to identify a particular cell, and not just to returen a number, so that you may incorporate it into another forular?

For example, =SUM(VLOOKUP("Green",A2:D9,3)VLOOKUP("Green",A2:D9,6)

or is this a bit much?

any help would be appriciated!!!

Olly

 

[Domenic]

First, since the table array A2:D9 only contains four columns, using 6 as the column index would return a #REF! error value. So assuming that you'd like to sum the corresponding values in Columns B, C, and D, try...

=SUMPRODUCT(VLOOKUP("Green",A2:D9,{2,3,4}))

Also, since the range lookup is omitted and defauls to 1, VLOOKUP looks for an approximate match, and requires the data in the lookup range to be sorted in ascending order. For an exact match, change the range lookup to 0 or FALSE...

=SUMPRODUCT(VLOOKUP("Green",A2:D9,{2,3,4},0))

Hope this helps!

 

[daniels012]

Not sure I understand by your example?

You do not want the value of the Vlookup?
What did you want? The cell coordinates? Like "D4"

Also, your example is a bit confusing because your range A2:D9 can not look over 6 columns if D is the last column in the range, it would be F??

Michael

 

[thesproing]

yeah, ignore the 6, mistake on my part,

Ok, what i am trying to do is to work out an average yearly wage, month by month for a number of different people. However, they start work at different months and the average needs to be calculated from their individual start date untill the same month of the following year, ie it might be from sept to sept for one person, or march to march for another.

One table contains the "look up" data, ie employee names and starting months, and the second table, or the "array" contains all of the seperate months, the employees and how much they have earnt in each month.

at the moment i am using the formula

=VLOOKUP("Melanie",Data!A3:M23,MATCH(C7,Data!A3:M3,FALSE))

which is giving me the value in the cell of the month that she started, but now i need to add the next 11 values in the same row, to get a total figure. if can do that then my problems are over!

hope thats a little clearer!!!

 

[thesproing]

yeah, ignore the 6, mistake on my part,

Ok, what i am trying to do is to work out an average yearly wage, month by month for a number of different people. However, they start work at different months and the average needs to be calculated from their individual start date untill the same month of the following year, ie it might be from sept to sept for one person, or march to march for another.

One table contains the "look up" data, ie employee names and starting months, and the second table, or the "array" contains all of the seperate months, the employees and how much they have earnt in each month.

at the moment i am using the formula

=VLOOKUP("Melanie",Data!A3:M23,MATCH(C7,Data!A3:M3,FALSE))

which is giving me the value in the cell of the month that she started, but now i need to add the next 11 values in the same row, to get a total figure. if can do that then my problems are over!

hope thats a little clearer!!!

 

[thesproing]

yeah, ignore the 6, mistake on my part,

Ok, what i am trying to do is to work out an average yearly wage, month by month for a number of different people. However, they start work at different months and the average needs to be calculated from their individual start date untill the same month of the following year, ie it might be from sept to sept for one person, or march to march for another.

One table contains the "look up" data, ie employee names and starting months, and the second table, or the "array" contains all of the seperate months, the employees and how much they have earnt in each month.

at the moment i am using the formula

=VLOOKUP("Melanie",Data!A3:M23,MATCH(C7,Data!A3:M3,FALSE))

which is giving me the value in the cell of the month that she started, but now i need to add the next 11 values in the same row, to get a total figure. if can do that then my problems are over!

hope thats a little clearer!!!

 

[thesproing]

yeah, ignore the 6, mistake on my part,

Ok, what i am trying to do is to work out an average yearly wage, month by month for a number of different people. However, they start work at different months and the average needs to be calculated from their individual start date untill the same month of the following year, ie it might be from sept to sept for one person, or march to march for another.

One table contains the "look up" data, ie employee names and starting months, and the second table, or the "array" contains all of the seperate months, the employees and how much they have earnt in each month.

at the moment i am using the formula

=VLOOKUP("Melanie",Data!A3:M23,MATCH(C7,Data!A3:M3,FALSE))

which is giving me the value in the cell of the month that she started, but now i need to add the next 11 values in the same row, to get a total figure. if can do that then my problems are over!

hope thats a little clearer!!!

 

[Domenic]

yeah, ignore the 6, mistake on my part,

Ok, what i am trying to do is to work out an average yearly wage, month by month for a number of different people. However, they start work at different months and the average needs to be calculated from their individual start date untill the same month of the following year, ie it might be from sept to sept for one person, or march to march for another.

One table contains the "look up" data, ie employee names and starting months, and the second table, or the "array" contains all of the seperate months, the employees and how much they have earnt in each month.

at the moment i am using the formula

=VLOOKUP("Melanie",Data!A3:M23,MATCH(C7,Data!A3:M3,FALSE))

which is giving me the value in the cell of the month that she started, but now i need to add the next 11 values in the same row, to get a total figure. if can do that then my problems are over!

hope thats a little clearer!!!

Did you see my post?

 

[thesproing]

yes thank, helped a lot!