• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

lookup with multiple corresponding value

jagger-meier

New Member
Hi,


My lookup value was shown more than once in source data, is there a way i could pick up all those values and reflect in my lookup?

i.e.

A - reference

B - Document Number (split into 2 or more, same reference)

3 - Amount (split into 2 or more, same reference)


I want to pick up all the Document Numbers or the Amount in my lookup, please lend a hand.


Thanks for your help,


jagger-meier
 
Jagger-meier

When you say lookup, do you mean add up or sum


If so you can use SUMIF, SUMIFS or SUMPRODUCT

http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/


If you mean lookup, you will need to add a column to give each a unique identifier

and then write a report somewhere else that will extract all the data using the field values, unique id and Row No's
 
Hi Hui,


i mean vlook up. i tried SUMIF already but it is good for the total of the amount only.

i need the Documents Numbers also, i tried vlookup(s) with "&" but the results are the same.


Thank you for your help and time.
 
You will need to add a column which will identify the rows that match your criteria

ie: =if(supplier="XYZ",max(column above me)+1, "")

and add another column that Ranks the previous column


Then you can write a report which looks up the values 1, 2, 3 etc and extracts the other data to make a report
 
Jagger-meier

Have a look at an example here


http://rapidshare.com/files/369805558/VLookup_Report__jagger-meier_.xls
 
Back
Top