How to overcome LOOKUP function problems?

[Guest]

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

 

[Aladin Akyurek]

Care to post the formula you tried?

 

[Guest]

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

 

[Guest]

Try:

=IF(ISNA(vlookup(A1,worksheet2!$A$1:$B$4,2,FALSE)),"Not Found",
vlookup(A1,worksheet2!$A$1:$B$4,2,FALSE))

 

[Aladin Akyurek]

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)),VLOOKUP(A1,worksheet2!$A$1:$B$4,2,0),"")

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

:

--

[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.

 

[Guest]

Hello,

Thanks for your help. I tried all the options. They work fine. Now I want to
alter worksheet 2 and include a total of the product price. I want to have B1
on worksheet 1 to pick up the sums.

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: 1 ; B2: $20
A3: 1 ; B3: $45
A3: 2 ; B3: $19
A4: 2 ; B4: $22
A5: 2 ; B5: $41

I want B1 on worksheet 1 to show the total of product '1', ie, $45. And B3
to show the show the total of $41.

Help appreciated.

Cheers,

Wendy

 

[Guest]

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

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)),VLOOKUP(A1,worksheet2!$A$1:$B$4,2,0),"")

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

:

--

[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.

 

[Aladin Akyurek]

Try a SumIf formula.

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

:

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)),VLOOKUP(A1,worksheet2!$A$1:$B$4,2,0),"")

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

:



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.

 

[Guest]

Thanks, Aladin. It works fine now. FYI, here is the end result.

=IF(ISNA(VLOOKUP(E2,'2005-7 Invoice'!$A$1:$B$138,2,FALSE)),"Not
Found",SUMIF('2005-7 Invoice'!$A$2:$A$136,'2005-7 PO'!E2,'2005-7
Invoice'!$B$2:$B$136))

Try a SumIf formula.

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

:

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)),VLOOKUP(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

:



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.