# Thread: Lookups / Match (Excel 2000)

1. ## Lookups / Match (Excel 2000)

The attached file will visually show what I am after in a more concise way than I can type it.

Using a match program I can find the row in File 'B' that corresponds to cells B5+C5 in my example. On that row, I then need to find where the number in D5 is and pick up the amounts that are in the next 2 columns to populate in cells E5 & F5.

I get close to doing this, but can't quite get the row specified into another match function.

2. ## Re: Lookups / Match (Excel 2000)

Something like this?

3. ## Re: Lookups / Match (Excel 2000)

Looks good, but unfortunately the required Uwyr (in File 'B') could be in any column - I should have put more sample data in for File 'B'

4. ## Re: Lookups / Match (Excel 2000)

Is this what you meant?

5. ## Re: Lookups / Match (Excel 2000)

Closer - but the 'A38420000' in File 'B' could be in any row. So I need something to find out where that is first.

6. ## Re: Lookups / Match (Excel 2000)

If I understand correctly (I wasn't sure since you did NOT have any data to "lookup" in the other rows, I assumed you would have some in your "full worksheet")

Create a Name: TableWidth equal to the width of the Uwyr/Amt_1/Amt_2 groups (you only listed 3 sets = 9, but I thought you might have more)

Try this in E5 (all one line)
=OFFSET(\$B\$20,MATCH(\$B\$5&\$C\$5,\$B\$20:\$B\$29,0)-1,MATCH(\$D\$5,OFFSET(\$B\$20,MATCH(\$B\$5&\$C\$5,\$B\$20:\$B \$29,0)-1,1,1,TableWidth),0)+1,1,1)

and this in F5: (all one line)
=OFFSET(\$B\$20,MATCH(\$B\$5&\$C\$5,\$B\$20:\$B\$29,0)-1,MATCH(\$D\$5,OFFSET(\$B\$20,MATCH(\$B\$5&\$C\$5,\$B\$20:\$B \$29,0)-1,1,1,TableWidth),0)+2,1,1)

The 1 problem I see is if the Uwyr value is also one of the Amt columns and it picks up that value. If that might be a problem, I would suggest turning the Awyr values into something like "Awyr85" and then match those.(the \$d\$5 would be replaced with "Awyr"&\$d\$5.

Steve

7. ## Re: Lookups / Match (Excel 2000)

Assign LTable as name to B20:K29 (in File [img]/forums/images/smilies/cool.gif[/img].

Assign LFields as name to B19:K19 (in File [img]/forums/images/smilies/cool.gif[/img].

In A5 (in File A) enter:

=SUMPRODUCT((INDEX(LTable,0,1)=\$B\$5&\$C\$5)*(LFields =\$D\$4)*(INDEX(LTable,0,0)=\$D\$5)*COLUMN(INDEX(LTabl e,0,0)))

In E5 (in File A) enter:

=VLOOKUP(\$B\$5&\$C\$5,LTable,\$A\$5,0)

In E6 (in File A) enter:

=VLOOKUP(\$B\$5&\$C\$5,LTable,\$A\$5,0)

Note 1. You need to make sure that C5 is text formatted before an "Add" entry.
Note 2. LTable can be defined as a name that refers to (A) a definite range or ([img]/forums/images/smilies/cool.gif[/img] by means of a dynamic formula.
If File A and File B refer to different workbooks, ([img]/forums/images/smilies/cool.gif[/img] would require that File B is open.

8. ## Re: Lookups / Match (Excel 2000)

Very clever use of the Index Array. I like your way better, it solves my problem with ensuring it is from a Uwyr column.

One comment, you made a Typo in your description (you have it correct on the spreadsheet.)
In E6 (in File A) enter:
=VLOOKUP(\$B\$5&\$C\$5,LTable,\$A\$5+1,0)

Steve

9. ## Re: Lookups / Match (Excel 2000)

If you'd be so kind to type in another number you'll see it works.

10. ## Re: Lookups / Match (Excel 2000)

Guys,

I can see how it works, but unfortunately when I copy the Index formula in, I get the value 0. The only thing I added was the reference to the other file.

If someone could spot where I've gone astray, I will then be able to stop headbutting my desk!!

I'm attaching the 2 files. FILE_A is my result area, BRIEF_22 is the source data

BRIEF_22 first

11. ## Re: Lookups / Match (Excel 2000)

...and now FILE_A

12. ## Re: Lookups / Match (Excel 2000)

I saw a problem with your SumProduct Formula:
=SUMPRODUCT((INDEX(LTable,0,1)=\$B\$5&\$C\$5)*(LFields =\$D\$4)*(INDEX(LTable,0,0)=\$D\$5)*COLUMN(INDEX(LTabl e,0,0)))

The last item:
COLUMN(INDEX(LTable,0,0))) is dependent on WHERE LTable is. Currently it is array of values 2 -11 (column B - K)
If you move it the columns will change and the lookup column will be wrong

This should fix it:
=SUMPRODUCT((INDEX(LTable,0,1)=Sheet1!\$B\$5&Sheet1! \$C\$5)*(LFields=Sheet1!\$D\$4)*(INDEX(LTable,0,0)=She et1!\$D\$5)*(COLUMN(INDEX(LTable,0,0))-COLUMN(INDEX(LTable,1,1))))+2

Steve

13. ## Re: Lookups / Match (Excel 2000)

Change your Ltable and Lfields to both start with Column B not Column A. You are doing a lookup of "Contract" not the "tm" as your formula indicates

Steve

14. ## Re: Lookups / Match (Excel 2000)

Use this formula:

=OFFSET(\$B\$20,MATCH(\$B\$5&\$C\$5,'[5-179998-Brief_22.xls]data'!\$B\$2:\$B\$11,0)-1,MATCH(\$D\$4&\$D\$5,'[5-179998-Brief_22.xls]data'!\$D\$1:\$BT\$1&'[5-179998-Brief_22.xls]data'!\$D\$2:\$BT\$2,0)+1,1,1)

Make sure you change the filename between the square brackets. Also make sure you have the correct number of rows (adapt \$B\$11 if needed).

15. ## Re: Lookups / Match (Excel 2000)

That's the ticket.

Haven't used INDEX before - hadn't needed to.

Thanks to all of you.

Page 1 of 2 12 Last

#### Posting Permissions

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