Lookups / Match (Excel 2000)

Topic

New Reply

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.

Thanks in advance

Reply | Quote

Replies

Something like this?

Reply | Quote

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’

Reply | Quote

Is this what you meant?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

…and now FILE_A

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Assign LTable as name to B20:K29 (in File .

Assign LFields as name to B19:K19 (in File .

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(LTable,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 ( by means of a dynamic formula.
If File A and File B refer to different workbooks, ( would require that File B is open.

Aladin

Reply | Quote

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

Reply | Quote

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(LTable,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)=Sheet1!$D$5)*(COLUMN(INDEX(LTable,0,0))-COLUMN(INDEX(LTable,1,1))))+2

Steve

Reply | Quote

That’s the ticket.

Haven’t used INDEX before – hadn’t needed to.

Thanks to all of you.

Reply | Quote

The gist is to have named ranges as few as possible, in particular ones that must be defined
by means of a dynamic formula using OFFSET, which is a volatile function. INDEX,
a very fast (non-volatile) function, suffices to access components of the range the defined name
refers to.

Aladin

Reply | Quote

Steve,

The observation is correct. I actually avoided the issue. I shouldn’t have.

I think the following would also take care of column insertions before the first column of LTable itself:

=SUMPRODUCT((INDEX(LTable,0,1)=$B$5&$C$5)*(LFields=$D$4)*(INDEX(LTable,0,0)=$D$5)*COLUMN(INDEX(LTable,0,0)))-(CELL(“Col”,B19)-1)

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

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

See the updated attachment.

Aladin

Reply | Quote

Subtracting AFTER the SUMPRODUCT (as you suggest) is better than mine (less calcs). I’m sure there are other ways. I would suggest though that
CELL(“Col”,LTable)
might be better than
CELL(“Col”,B19)
to keep the reference to the named range instead of a cell location.

Steve

Reply | Quote

Yes. I like that.

Aladin

Reply | Quote

Just for clarification, shouldn’t it be
CELL(“Col”,LFIELDS)

??

Reply | Quote

It doesn’t matter, they both start in the SAME column. I used Ltable since you are getting the column numbers from ltable

Steve

Reply | Quote