• 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.

VLOOKUP that returns unique instances rather that the first??? SUMIFS/IF(AND ?

tarynmahon

tarynmahon

Member
I have a template that our Marketing Department records invoices on, in order to have them use the correct source codes I also had data validation rules mixed with VLOOKUP's pulling through the correct codes, unfortunately the problem occurs when the same source is used for different entities that should pull through a different source code, obviously due to using VLOOKUP's it will only pull through the first instance, eg.

COLUMN A(ENTITY), COLUMN B(SOURCE NAME), COLUMN C(SOURCE CODE)

LIS, SUPPLIER A, *FORMULA TO RETREIVE CODE BASED ON COL(B)*

LIS, SUPPLIER B, *FORMULA TO RETREIVE CODE BASED ON COL(B)*

SDL, SUPPLIER A, *FORMULA TO RETREIVE CODE BASED ON COL(B)*

SDL, SUPPLIER C, *FORMULA TO RETREIVE CODE BASED ON COL(B)*


Each of these lines should pull through a unique Source Code from the following list;

LIS, Supplier A, CODE1

LIS, Supplier B, CODE2

LIS, Supplier C, CODE3

LIS, Supplier D, CODE4

SDL, Supplier A, CODE5

SDL, Supplier B, CODE6

SDL, Supplier C, CODE7

SDL, Supplier D, CODE8


The Source Codes for the above example should return;

COLUMN A(ENTITY), COLUMN B(SOURCE NAME), COLUMN C(SOURCE CODE)

LIS, SUPPLIER A, CODE1

LIS, SUPPLIER B, CODE2

SDL, SUPPLIER A, CODE5

SDL, SUPPLIER C, CODE7


I've tried to do a SUMIFS and an IF(AND but I cant seem to get them to work, any ideas please?
 
Also, I should probably add that column B is the Data Validation, there should be a way of Marketing being able to choose the correct source as per the Entity, ideally (although I dont know how it can be done) if the Entity is LIS the Data Validation only lists the LIS Sources, and the same for SDL as there will not always be the same sources in both Entities.


Thanks
 
Hi tarynmahon,


I am not sure I understood your second post, but you could try something like the following to lookup the data per your first post:


The sample source data is assumed to be setup as follows:

[pre]
Code: 
K2:K9	L2:L9	        M2:M9
LIS	Supplier A	CODE1
LIS	Supplier B	CODE2
LIS	Supplier C	CODE3
LIS	Supplier D	CODE4
SDL	Supplier A	CODE5
SDL	Supplier B	CODE6
SDL	Supplier C	CODE7
SDL	Supplier D	CODE8
[/pre]
The formula to return the source codes from M2:M9, based on criteria match in K2:K9 and L2:L9 would be (entered with Ctrl+Shift+Enter):

=INDEX($M$2:$M$9,MATCH(1,($K$2:$K$9=A2)*( $L$2:$L$9=B2), 0))


Here A2 and B2 are what you are trying to match in the source list.


(Please feel free to use error trapping with IFERROR(...) as needed, to handle cases when the source codes could not be found.)


Hope this helps.


Cheers,

Sajan.
 
Tarynmahon


You may also want to look at the technique I just used at: http://chandoo.org/forums/topic/screwed-up-vlookup

The one formula adjusts for both rows and column offsets and hence 1st, 2nd 3rd etc places across multiple columns
 
Hui, I am unable to view things in dropbox, could you please email me your example?

I think you have my email address
 
Good day tarynmahon


This is the formula Hui is referring to


=IFERROR(INDEX($B$3:$E$41, SMALL(IF($A$3:$A$41=$J$15,ROW($A$3:$A$41)-2), COLUMNS($J$16:J16)),ROWS($J$16:J16)), "") Change the cell reference to suit. Array formula so enter by Ctrl+Shift+Enter
 
You must log in or register to reply here.
Back
Top