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

3 Way Lookup using Index and match formula

A

ashl

Member
Hi Guys,


I'm trying to figure out a way of doing a 3 way lookup formula without using offset. I was using an offset formula and it sometimes worked and then all of a sudden it didn't.


My next try is a index and match formula. As I have two rows to lookup I've joined them in my formula with an ampersand.

So it goes {=index($B$84:$Y$111;match(I$5&$B10;$B$82:$Y$82&$B$83:$Y$83;0);match($C$3;$A$84:$A$111;0))} and used an array.

This worked for the first value as it was referencing the firt row and first column.


When I press F9 on the match section of the formula I can see it has joined the two rows. However, I've just noticed it keeps giving me zero as it is always looking at the first row and colum although the formula says to look at the other rows.


What am I doing wromg plz?


Thanks :)
 
It probable has to do with the way that I've set it up.


In my table I have a column of products going down. Then I have one row saying whether it is fruit, vegetable or dessert and the next row underneath saying the colour and then after those 2 rows I have the values.


Does it have something to do with that?
 
I think you have your row and column arguements swapped. This part

Code: 
match($C$3;$A$84:$A$111;0))

Appears to be determining the row to look at, but it's in the column argument of the INDEX function.


For reference:

http://chandoo.org/wp/2010/11/02/multi-condition-lookup/
 
Hi ashl,


Can please you upload a sample workbook explaining your goal?


Also note that some of the users may post comma based formula instead of semicolon based as you've posted. I'd guess you are somewhere in EU.
 
Hi,


Try sumproduct.


Assuming Products in A1:A10, Fruit etc in B1:D1, colour in B2:D2 and values in B3:D10.


=SUMPRODUCT((A1:A10="Product1")*(B1:D1="Fruit")*(B2:D2="Red")*(B3:D10))


Jai
 
Good day ashl

If you look at the upload you will find a worksheet with a Vlookup that returns more than one data set, very easy to modifie to include as you require


https://dl.dropbox.com/u/75495784/Multi_Vlookup.xlsx
 
Hi Ive uploaded an example of what I'm trying to do, hope it helps :)


https://www.box.com/s/muhdhwsiz517yfafoy5m
 
Oh thanks bobhc sory we mustve posted at the sametime. I unfortunately can't access it but I'll try when I get home.


Thank u :)
 
In Cell F22 paste following formula:

=IFERROR(INDEX($B$5:$H$11;MATCH($F$20;$A$5:$A$11;0);MATCH(F$21&$E22;$B$3:$H$3&$B$4:$H$4;0));0)


Copy formula down and across!
 
Hi,


You can still use OFFSET(), enter in F22 and drag down/right:


Code: 
=IFERROR(OFFSET($A$4,MATCH($E$20,$A$5:$A$11,0),MATCH(F$21&$E22,$B$3:$H$3&$B$4:$H$4,0)),"")


Press Ctrl+Shift+Enter


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