Excel Lookup question 2

[kabushnell]

I have two excel spreadsheets that I am trying to combine some of the information from them.

One spreadsheet contains and ID# in column A, an Acct# in column B and a $amount in column C.

The other spreadsheet has the same ID numbers in column A, row 1 contains the Acct#'s with columns B-X with the dollar amounts under the Acct#'s.

I want to take the corresponding amount that matches the account number in spreadsheet two and put in Column C of Spreadsheet 1. Kind of like a Vlookup I guess but not really. I can't figure out an easy way to do this.

Any ideas??

 

[henio]

Hi,

you need to use the INDEX and MATCH functions.

eg

sheet1
colA
ID Acct Amnt
10 100 =index(=INDEX(Sheet2!$A$1:$D$2,MATCH(Sheet1!A2,Sheet2!A:A,0),MATCH(Sheet1!B2,Sheet2!$A$1:$D$1,0)) will return 1

sheet2
colA
ID 100 200 300
10 1 2 3

hth

henio

 

[jfhewitt]

Why not the easy way...just link the cells. Copy cell from sheet a to sheet b, and use "paste special/link". I find INDEX and MATCH cumbersome unless you must use arrays.