match and extract

[Dinesh]

Hi,

I have one worksheet, two different tab.
There are two customer number columns in Sheet1. One of the two would have
the customer number and the other one would be blank. Column C has an amount.
Col A Col B Col C Col D (Formula to bring the amount from sheet2)
245600 50000
138956 25000
965241 14589

Sheet2 has one customer number column and an amount column.
Col A Col B
245600 50000
138956 25000
965241 14589
In Sheet1, I like to bring in the amount of Sheet2 based on matching of the
customer number of Sheet1 which is in two different columns.

Thanks,
Dinesh

 

[Max]

One way using index/match, with minimal error trapping
Data assumed running in row2 down
In Sheet1,
In C2:
=IF(A2="",INDEX(Sheet2!B:B,MATCH(B2,Sheet2!A:A,0)),INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0)))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Teethless mama]

Try this:

=VLOOKUP(MAX(A1:B1),Sheet2!A:B,2,)

copy down

 

[T. Valko]

Try this...

One of the two would have the customer
number and the other one would be blank

Assuming that your customer numbers are in fact numeric numbers and not
alpha-numeric text strings.

Assume on Sheet1 your customer *numbers* can be in either column A or column
B (but will never be in both at the same time).

Sheet2:

Column A = customer numbers
Column B = amounts

Enter this formula on Sheet1 in cell C1:

=SUMIF(Sheet2!A:A,SUM(A1:B1),Sheet2!B:B)

Or:

=SUMIF(Sheet2!A:A,A1+B1,Sheet2!B:B)

Copy down as needed.

 

[dinesh]

Try this...


Assuming that your customer numbers are in fact numeric numbers and not
alpha-numeric text strings.

Assume on Sheet1 your customer *numbers* can be in either column A or column
B (but will never be in both at the same time).

Sheet2:

Column A = customer numbers
Column B = amounts

Enter this formula on Sheet1 in cell C1:

=SUMIF(Sheet2!A:A,SUM(A1:B1),Sheet2!B:B)

Or:

=SUMIF(Sheet2!A:A,A1+B1,Sheet2!B:B)

Copy down as needed.

--
Biff
Microsoft Excel MVP



HI,

I made a mistake in which sheet do i need an answer? Actually I like to
match the Sheet2's customer number and bring the amount of sheet1 into
sheeet2 which is the opposit of what I stated earlier.

Thanks,
Dinesh