How to overcome LOOKUP function problems?

[Wendy]

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?

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

[Wendy]

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
>

[Duke Carey]

Try:

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


"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
> >

[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),"")


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.

[Wendy]

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


"Duke Carey" wrote:

> Try:
>
> =IF(ISNA(vlookup(A1,worksheet2!$A$1:$B$4,2,FALSE)),"Not Found",
> vlookup(A1,worksheet2!$A$1:$B$4,2,FALSE))
>
>
> "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
> > >

[Wendy]

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)),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
> >
> > "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.
>

[Aladin Akyurek]

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)),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
>>>
>>>"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.

[Wendy]

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))


"Aladin Akyurek" wrote:

> 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)),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
> >>>
> >>>"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.
>