Multiple Item Lookup

C

Chad F

I have a source data sheet named "Import" where I paste data in from
Quickbooks. On another sheet in column A, I am searching the source data in
Column U (which is a special price column for items that are on sale this
qtr.) for any row that has a value greater than 0. If so, then it pulls in
the item number from the source data coumn C.

This is the formula in A1:
=IF((Import!U1>0),Import!C1+ROW()/10^10,"")
copied down to A4000

The above formula works great, but, in column B, I am trying to remove the
gaps or rows without a result from the above formula. In other words, if
there are 50 results in column A, then they will display in B1 to B50.

This is the formula in B1:
=IF(ROW()>COUNT(A:A),"",INDEX(Import!C:C,MATCH(SMALL(A:A,ROW()),A:A,0)))
copied down to B4000

The result that I am getting in B1 to B4000 is "#VALUE", and I don't know
why. This formula works when I search some of the other data. Can someone
help?

Thanks,
Chad
 
S

Shane Devenshire

Hi,

Don't know exactly what your data looks like but the second formula you are
using will return a #VALUE error if any cells in the range A:A or C:C return
#VALUE.

The first formula will return a #VALUE error if C1 is not a number or a blank:

=IF((Import!U1>0),Import!C1+ROW()/10^10,"")

If there are formulas in column C of the Import sheet which return "" that
will also cause the 1st formula to return VALUE.
 
M

Max

Chad,
Suggest that you apply autofilter on the upstream formulated criteria col
for quick diagnosis. Just scroll right down in the autofilter droplist, check
for error values such as #N/A, #VALUE, etc. These are the usual culprits
which break the formulas flow. And the underlying causes would usually lie
further upstream, in the source data's consistency. It may actually be mixed
text (alphas) / nums data while you had thought it's pure nums/text nums
right down the col (eg in Import's col C).

Instead of:
=IF((Import!U1>0),Import!C1+ROW()/10^10,"")
Click to expand...

Try:
=IF(Import!U1>0,IF(ISNUMBER(Import!C1+0),Import!C1+ROW()/10^10,""),"")

Above should give you better results
in the face of mixed source data in Import's col C
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
 
C

Chad F

Max,

Thanks! That did it, and it works great.

I am about done with this file that you have been helping me on. It is
pretty big now -- about 35MB. This is helping to shave hours off of
generating our monthly order guide that we send out to our customers. Thanks
for all of your help. I could not have done it without you.

Best Regards,
Chad
 
C

Chad F

Max,

Type in "Vendor Lookup" in the search box, and see if you can help me with
that one.

Thanks,
Chad
 

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

Similar Threads

Vendor Lookup 5
Consolidation Table from Different Excel Tabs with Different Format 1
Need Help with a VBA subroutine 0
Mail Merge Help 1
What is the "--" (double dash) in MS Excel formula? 2
VBA Coding Help for Beginner 0
Please Help 3
Multiple IF Statement in Word 5

Top