0
3335

# vlookup - amend the formula

vlookup - amend the formula

• ### Winding up w/ overdrawn directors loan acc. <£25k

I have three columns of data A, B and C. Col A contains the results and col C the references.

How can I use a vlookup formula to look up column C, and return results in col A. As far as I can determine the sourced lookup reference must be on the far left hand side of the source data range, not in the middle or RHS....but I bet someone will know.

I don't want to change the order of cols A, B and C.

Michael Ung

### Replies

05th Jun 2007 17:42

Use Index/Match
Michael

As you correctly point out, you can't use Vlookup because of the structure of your data. However, you can use a combination of Index and Match to achieve the same results but without the structural limitation:

=INDEX(\$A\$2:\$A\$100,MATCH(D1,\$C\$2:\$C\$100,0))

which will lookup the value in D1 for its matching counterpart in the C column and return the corresponding value in the A column.

Hope this helps!

Richard

Thanks (0)
By mung1
07th Jun 2007 10:53

Richard - thank you
I had been fiddling around trying to nest "OFFSET" in a lookup.

Your solution is just what I was looking for.

Many thanks

Thanks (0)
By Anonymous
18th Jun 2007 12:43

As an alternative sumif() may work
If the data in the 'A' column is numeric you could also try the 'SUMIF() function.

=SUMIF(C:C,D:D,A:A)

will match items in the 'C' column against those in the 'D' column and return the Sum of the corresponding items in the 'A' column.

If your references in the 'C' column are unique the sum will be of the single matching value from the 'A' column. If not it will give a total for each reference.

Thanks (0)