PDA

View Full Version : Solved: Lookup (or other) solution



mdmackillop
08-30-2006, 08:24 AM
Hi All.
I'm looking for a formula solution to enter into column 14 of BoM.xls which will fill in the values for the corresponding Order No. and Page items contained in Val.xls

Shazam
08-30-2006, 09:14 AM
Hi mdmackillop,

Input formula in cell N3 and copied down

=INDEX([Val.xls]BoQ!$G$16:$FN$5000,MATCH(A3,[Val.xls]BoQ!$B$16:$B$5000,0),MATCH(D3,[Val.xls]BoQ!$G$1:$FN$1,0))

Bob Phillips
08-30-2006, 09:44 AM
slight presentational amendment

=IF(OR(ISNA(MATCH(D3,[Val.xls]BoQ!$G$1:$FN$1,0)),ISNA(MATCH(D3,[Val.xls]BoQ!$G$1:$FN$1,0))),"",
INDEX([Val.xls]BoQ!$G$16:$FN$5000,MATCH(A3,[Val.xls]BoQ!$B$16:$B$5000,0),MATCH(D3,[Val.xls]BoQ!$G$1:$FN$1,0)))

Shazam
08-30-2006, 10:15 AM
slight presentational amendment

=IF(OR(ISNA(MATCH(D3,[Val.xls]BoQ!$G$1:$FN$1,0)),ISNA(MATCH(D3,[Val.xls]BoQ!$G$1:$FN$1,0))),"",
INDEX([Val.xls]BoQ!$G$16:$FN$5000,MATCH(A3,[Val.xls]BoQ!$B$16:$B$5000,0),MATCH(D3,[Val.xls]BoQ!$G$1:$FN$1,0)))


Hi xld,


I dont know how large mdmackillop file is but will this formula will be better? Because its not computing twice. It should be faster.

Input formula in cell N3

=LOOKUP(10^307,CHOOSE({1,2},0,INDEX([Val.xls]BoQ!$G$16:$FN$5000,MATCH(A3,[Val.xls]BoQ!$B$16:$B$5000,0),MATCH(D3,[Val.xls]BoQ!$G$1:$FN$1,0))))

Then custom format cell:

0;-0;;@

and copied down



What do you think?

mdmackillop
08-30-2006, 01:06 PM
Thanks both,
I'd got as far as creating a match for the column and the row in separate columns, and was trying to figure out an indirect function to make use of these addresses, but my brain was hurting. Both solutions work well with a data source of 1200+ rows, filling down for 1700 rows. Now all I have to do is track down the errors in my raw data, and I'm afraid there is no VBA solution for that!