Vendor Lookup

C

Chad F

On a test sheet, I have this formula in A1:
=IF(Import!M2="BIC USA
Inc.",IF(ISNUMBER(Import!A2+0),Import!A1+ROW()/10^10,""),"")

The above formula is copied down to A4000. It pulls in any item number that
is associated with "BIC USA Inc." into the corresponding row on the test
sheet.

On that same sheet, I have this formula in B1:
=IF(ROW()>COUNT(A:A),"",INDEX(Import!A:A,MATCH(SMALL(A:A,ROW()),A:A,0)))

The above formula is copied down to B4000. It removes the empty rows in
column A so that the first returned value appears in B1, then B2, B3, etc.

This works great if both of these formulas start in row 1, however, my
problem is that the sheet that I actually want to use this on has data in
A1:B18. So I am trying to begin my formulas in A19 and B19, but I am having
no luck (even if I try to modify them a little).

Can anyone help?

Thanks,
Chad
 
T

T. Valko

begin my formulas in A19 and B19,
=IF(ROW()>COUNT(A:A),"",INDEX(Import!A:A,MATCH(SMALL(A:A,ROW()),A:A,0)))

Replace this:

=IF(ROW()>COUNT(A:A)

With this:

=IF(ROWS(B$19:B19)>COUNT(A:A)
 
M

Max

Chad,

These adjustments should work fine

In A19:
=IF(Import!M2="BIC USA
Inc.",IF(ISNUMBER(Import!A2+0),Import!A1+ROW()/10^10,""),"")

In B19:
=IF(ROWS($1:1)>COUNT($A$19:$A$4000),"",INDEX(Import!A:A,MATCH(SMALL($A$19:$A$4000,ROWS($1:1)),$A$19:$A$4000,0)+1))
Copy A19:B19 down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
 
T

T. Valko

Ooops!

I missed the other instance or ROW() that also needs to be changed.

Here's the whole thing:

=IF(ROWS(B$19:B19)>COUNT(A:A),"",INDEX(Import!A:A,MATCH(SMALL(A:A,ROWS(B$19:B19)),A:A,0)))
 
T

T. Valko

Or use ROWS($1:1).

Yeah, but if an entry is made anywhere on row 1 that formula will
recalculate.

It's better to use *specific cell references*.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top