VLOOKUP not giving apt results....

Hi All,

I need to fetch data related to quantity of products bought.

Workbook 1: Has list of products say from Column A2 cell to A1669. B2 has quantities.

Let's say A15 to A23 has "Apple" and B15 to B23 has a quantity value for it.

Workbook 2: Has "Apple" in Column H again H2 to H10.

How can I look-up the quantity of A15 in B15 from workbook 1 to column G cell G2 in workbook 2, similarly from A16 in B16 to G3...?

Also, the data at time isn't in a chrono order. I don't have another identifier (unique) between the sheets.

The Vlookup I'm using is picking the quantity for B15 only and pasting it all across G2 to G10 in workbook 2

Please suggest...

if you are looking to add together all the "apple" quantities, use sumif() or sumifS().

if this is not what you want, i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, what your expected outcome is, and how you arrived at that

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

Originally Posted by FDibbins

if you are looking to add together all the "apple" quantities, use sumif() or sumifS().

if this is not what you want, i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, what your expected outcome is, and how you arrived at that

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

I don't need to sumif or sumifs - just the quantity for each cell from workbook 1 to Workbook 2 is to be populated.

I'll upload the file shortly.

Thanks!

Originally Posted by Sindhus

vlookup always return the first matching cell only. You need to use supporting column to generate your output.

Hmmm...I thought as much. Is there anyother way without a supporting column?

If not, then I guess I have the solution in place.

Please suggest.

Thanks for your inputs:-)

Originally Posted by Sindhus

Or you can try array formula like this..

Formula:

Please Login or Register  to view this content.

ColA refers to your Worksheet1 column a
ColB refers to worksheet1 column b
A1 is worksheet2 lookup value

The formula should be entered with Ctrl+Shift+Enter

Thank you sir!

I'll apply this and get back either to close the thread as "Solved" or with any query. In all likelyhood it will be the former, Im sure:-)

Have a good Sunday:-)