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

INDEX, MATCH, LOOKUP - help

M

madmarki

New Member
Hi Chandoo,


please look at the "sales figures.xlsx" workbook:


https://skydrive.live.com/redir.aspx?cid=a609bc90f0bd6d0c&resid=A609BC90F0BD6D0C!404


or if that link doesn't work (something to do with the !) - try http://goo.gl/HL1Ei


There are notes on the OUT tab that explain what my aim is. I am trying to get the desired results using INDEX andor MATCH andor LOOKUP but I'm getting confused.


Can you help?


Thanks

Mark
 
Formula:

=IF(ISNA(MATCH($C4,IN!$D$3:$I$3,0)),"",INDEX(IN!$D$4:$I$6,MATCH(D$3,IN!$C$4:$C$6,0),MATCH($C4,IN!$D$3:$I$3,0)))


Note that this formula will return a 0 for legitimate days where nothing was entered. If you need to suppress this, you can use a custom format similar to:

#;#;;@


How it works?

The first check in the IF function checks if the date (c4) is within the list of dates (D3:I3). If it's not that (returns #N/A), then show blank.

If it is there, we need to locate the sales figure within the indexed array (D4:I6). To do this, think of it was needing an x and y cordinate. These are found by using 2 MATCH function to determine which name (aka, how far down) and which date (aka, how far across) to look within the indexed array.
 
#Luke M - many thanks for the speedy reply and simple explanation.


I hadn't come across the IN function/formula before, and as far as i can see its not in the Excel Help File, so could you also explain the syntax for IN please?


I use the IN function with Access regularly, so it was one of the first things I looked for when trying to create this formula.
 
IN is your sheet name, not a function. =P

The complete address for cells of concern on sheet IN is:

IN!D3:I3


The other cells could have their sheet reference "OUT!$C4", but it makes things wordy, and occasionally causes weird things:

http://spreadsheetpage.com/index.php/oddity/sorting_oddity_bug/
 
Hi Luke,


> IN is your sheet name, not a function. =P


yeah I realised that on my way home last night - DOH!


Thanks again

Mark
 
You must log in or register to reply here.
Back
Top