# Can Someone Help me With a Nested VLOOKUP

1. ## Can Someone Help me With a Nested VLOOKUP

I have the following Data

Sheet 1 Sheet
2
Column A Column B Column C Column A
Column B Column C
556859 BF144256 456879
BF556982 \$55.22
456879 BF556982 556859
BF144256 \$68.23
456879 BF224896 456879
BF224896 \$25.32
456879 BF364896 456879
BF364896 \$35.45

I have a woorkbook with 2 tabs that are essentially, Column A in each
tab contains Invoice #, column B in each tab contains part #, and
Column C in sheet/tab 2 contains Avg Cost. I need a formula in column
C of sheet 1 that will return the Average cost that is associated with
a particular Invoice# and Part #. So I need a formula that will say
that.....If Column a and Column B of sheet 1 equals column A and column
B of sheet 2 return Column C on sheet 2 for the line that matches. A
vlookup wont work because there are invoices that have multiple part
numbers on them that is why i need a formula that will compare both
Invocie and part number for matches.

Here is what I have so far, it comes up N/A not sure if I am close or
way off base let me know if you can help
=IF((AND(VLOOKUP(A3,Sheet1!A2:C570,1,FALSE),VLOOKUP(B3,Sheet2!B2:J570,1,FALSE))),"",C1:C570)

Thanks,
Lee

2. ## Re: Can Someone Help me With a Nested VLOOKUP

Assuming data starts in row1 down in both Sheets 1 and 2

In Sheet1,

Put in C1, then array-enter the formula
i.e. press CTRL+SHIFT+ENTER

=INDEX(Sheet2!\$C\$1:\$C\$100,
MATCH(1,(Sheet2!\$A\$1:\$A\$100=A1)*(Sheet2!\$B\$1:\$B\$100=B1),0))

Copy C1 down

Adapt to suit (eg. the ranges in Sheet2)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
<lee.dykeman@monro.com> wrote in message
> I have the following Data
>
> Sheet 1 Sheet
> 2
> Column A Column B Column C Column A
> Column B Column C
> 556859 BF144256 456879
> BF556982 \$55.22
> 456879 BF556982 556859
> BF144256 \$68.23
> 456879 BF224896 456879
> BF224896 \$25.32
> 456879 BF364896 456879
> BF364896 \$35.45
>
>
> I have a woorkbook with 2 tabs that are essentially, Column A in each
> tab contains Invoice #, column B in each tab contains part #, and
> Column C in sheet/tab 2 contains Avg Cost. I need a formula in column
> C of sheet 1 that will return the Average cost that is associated with
> a particular Invoice# and Part #. So I need a formula that will say
> that.....If Column a and Column B of sheet 1 equals column A and column
> B of sheet 2 return Column C on sheet 2 for the line that matches. A
> vlookup wont work because there are invoices that have multiple part
> numbers on them that is why i need a formula that will compare both
> Invocie and part number for matches.
>
> Here is what I have so far, it comes up N/A not sure if I am close or
> way off base let me know if you can help
>

=IF((AND(VLOOKUP(A3,Sheet1!A2:C570,1,FALSE),VLOOKUP(B3,Sheet2!B2:J570,1,FALS
E))),"",C1:C570)
>
> Thanks,
> Lee
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1