Try a SumIf formula.

Wendy wrote:
Hello Aladin,

Thanks for your help. But I have changed worksheet 1 and 2 a bit to include
a 'sum' of the product prices. The formula you gave me is no longer valid as
I want to pick up the sum's as oppose to the first matching entry on
worksheet 2.

Please take a look at the below.

Thanks for your help.

Cheers,
Wendy

"Aladin Akyurek" wrote:


Two options...

(a) Since A1:B4 on worksheet2 is sorted in ascending order on column A,
a lookup formula can be invoked if the sort order can be kept in
ascending order:

In B1 on worksheet1 enter & copy down:

=IF(A1=worksheet2!$A$1,F(LOOKUP(A1,worksheet2!$ A$1:$A$4)=A1,LOOKUP(A1,worksheet2!$A$1:$B$4),"")," ")

(b)

=IF(ISNUMBER(MATCH(A1,worksheet2!$A$1:$A$4,0)),V LOOKUP(A1,worksheet2!$A$1:$B$4,2,0),"")


Wendy wrote:

Hello Aladin,

I want to match data from worksheet 1 and 2, and return the data in
worksheet 1 B1 to B4. Here are the info:

Worksheet 1
Column A contains product code
A1: 0
A2: 1
A3: 2
A4: 3

Worsheet 2
Column A contains product codes and column B contains price
A1: 1 ; B1: $25
A2: 3 ; B2: $20
A3: 4 ; B3: $19
A4: 5 ; B4: $22

I want to match age to name in worksheet 1. Here is the formula:
=LOOKUP(A1,'worksheet2A1:A4', 'worksheet2B1:B4')

Here are my response:
B1 = N/A#
B2 = $25
B3 = $25
B4 = $20

As you can see, B3 should be blank as it doesn't appear in worksheet 2.

How can I make it to show the correct data?

Thanks for your help.

Cheers,

Wendy

"Aladin Akyurek" wrote:



Care to post the formula you tried?

Wendy wrote:


Hello,
I am trying to match data from 2 worksheets using LOOKUP functions. I have
arranged the data in ascending order. Here is my problem:
Entry 1, 2, 3 shown up as 'n/a#' which is fine because the are supposed to
be 'no match'.
Entry 7, 8 , 9 are supposed to show 'no match' but returned with the data
in entry 6.
Which means LOOKUP function isn't working for me. I tried VLOOKUP, it just
picks up the first avaliable data in the worksheet. But I need to last
available data. That is no good to me either. I thought about incorporating
'IF' funcation with the 'VLOOKUP' function to get the results I want.
Unfortunately, I am not that good with Excel.
Can anyone help me?
Thanks,
Wendy

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.


--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.