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

Offset with index and match

odartey

odartey

Member
I have data which as contract numbers, destinations and quantity.

I want to lookup a destination so as it returns all contract numbers that matches it.

eg,if i look up london from the table and it has two or more contract nos,it should drop them out for me.

I have used the combination of the index, match and offset,but it all returns a single contract number.

Can anyone help.

thanks
 
Assumptions:

Contract numbers in Col A, destinations in col B.

Destination criteria in cell D2


Array formula (confirm using Ctrl+Shift+Enter):

=IF(COUNTIF($B:$B,$D$2)<ROWS(A$1:A1),"",INDEX(A:A,SMALL(IF($B$2:$B$10=$D$2,ROW($B$2:$B$10)),ROW(A$1))))


Change the range size of B2:B10 as needed. Copy this down as far as would ever be needed. Unused formulas will display blank.
 
thanks man.the formula still workd just as the index and match,still given me a single contract no to a destination.

please look a the table below to see if u can do more.thanks

eg, Contract Nos Destination

0034 Bangkok

0056 London

0078 Bangkok

0098 London

Criteria:

Bangkok >>0034 London >>0056

0078 0098
 
I don't understand your question. =(

Did you copy the formula down? Did you make it an array formula?
 
Yes i did base on your assumption,and the result i had is what i have pasted below.eg. Instead of Holland giving me two diff contract nos, it is giving me just one same contract no thru out

Contract Nos Dest

967888 London Holland 456678

456678 Holland 456678

678909 London

453212 Holland
 
Doh. My apologies, I see my error. The last argument should be

ROW(A1)

not

ROW(A$1)


Making that change and then copying done should solve it. =(
 
Luke-

You did a similar formula for me a while back, and, I'm wondering if you can walk through the logic of he 2nd half of the IF statment: SMALL(IF($B$2:$B$10=$D$2,ROW($B$2:$B$10)),ROW(A$1))))


From what I can gather, it says take the smallest value in column B if a value from B2:B10= D2, but, after that is where I fall off with the whole use of "ROWS" & "ROW". You seem to use it a lot for certain things (and they always work! :))
 
Sure thing 3G.

The IF statement says "IF the value in col B is what your looking for (in this case, D2), then give me the ROW number for that cell". So, let's we have an array like this:

a,b,c,a,e,a,b,c,d

In cells b2:10. If D2 is "a", the IF statement will return this array:

2,FALSE,FALSE,5,FALSE,7,FALSE,FALSE,FALSE


Now, this feeds into the SMALL function. The SMALL function will ignore all the text/boolean values, so it's just looking at this:

2,5,7

The ROW(A1) at the end (again, no dollar sign...mistake on my part) is there as a counter. In the first formula, this will evaluate to 1. When you copy the formula down, this will evaluate to 2, then 3, etc.


So, using our counter, the SMALL function will take the 1st smallest value from the array (2,5,7) and then INDEX that into col A. Aka, get the 2nd row from col A. The next formula down will take 2nd smallest, and thus look at row 5 in col A.


BTW, the ROWS at the beginning of overall formula is to see check when we've gotten all the records displayed. By counting how many formulas/cells/rows have been used so far and comparing to total number of records (usually found using COUNTIF), the formula can determine whether it should be blank or not.
 
You must log in or register to reply here.
Back
Top