# VLOOKUP

#### thesproing

##### Board Regular
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

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### Domenic

##### MrExcel MVP
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

##### Well-known Member
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

##### Board Regular
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

##### Board Regular
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

##### Board Regular
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

##### Board Regular
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

##### MrExcel MVP
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

##### Board Regular
yes thank, helped a lot!

Replies
10
Views
862
Replies
1
Views
185
Replies
1
Views
575
Replies
15
Views
1K
Replies
1
Views
176

1,191,529
Messages
5,987,116
Members
440,080
Latest member
drhorn4908

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back