How to do a Two-Way Lookup in Excel?

Excel spreadsheets support lookup functions that return a value from a table by looking up another value in the table. The vlookup and hlookup functions in Excel work as one-way lookups. This article helps you to perform a two way lookup.

The Pi
getting back with an ex>

cture below shows a simple example:

The first table shows products, Price of the product and discount on number of products purchased. The second table shows name of the person, product purchased, number of products purchased and discount applicable.

A formula is used in column E of second table to get the discount percentage from the first table as shown in the picture below:

The formula in cell E15 looks up the values of cells C15(Product) and D15(# of Products) in the first table and returns the corresponding value from the table.

The formula in E15 is:

=IFERROR(INDEX($D$4:$F$12,MATCH($C15,$B$4:$B$12,0),MATCH($D15,$D$3:$F$3,0)),”-”)

Description of the formula:

The above formula uses the INDEX function, which consists of three arguments.

The first argument gives the entire range of ‘Discount% on # of products’ from first table($D$4:$F$12).

=IFERROR(INDEX($D$4:$F$12,MATCH($C15,$B$4:$B$12,0),MATCH($D15,$D$3:$F$3,0)),”-”)

The second argument is MATCH function. MATCH ($C15,$B$4:$B$12,0)

viagra online pharmacy

It matches the ‘product’ in cell C15 of second

sale viagra

table with the ‘products’ in cells B4 to B12 of first table and returns the ‘Product.

=IFERROR(INDEX($D$4:$F$12,MATCH($C15,$B$4:$B$12,0),MATCH($D15,$D$3:$F$3,0)),”-”)

The third argument is also a MATCH function. MATCH ($D15,$D$3:$F$3,0).

It matches the ‘# of products’ in cell D15 of second table with the ‘Discount% on # of products’

in cells D3 to F3 of first table and returns the ‘# of products’.

=IFERROR(INDEX($D$4:$F$12,MATCH($C15,$B$4:$B$12,0),MATCH($D15,$D$3:$F$3,0)),”-”).

In this way the above formula is used as a two way lookup formula to retrieve the value required.

 

 

 

[Bloglines] [del.icio.us] [Digg] [diigo] [Facebook] [Furl] [Google] [kirtsy] [Ma.gnolia] [MySpace] [Propeller] [Reddit] [Simpy] [Sphinn] [StumbleUpon] [Technorati] [Windows Live] [Yahoo!]


16 Comments

  • By Uday, May 2, 2012 @ 12:40 pm

    Another formula for the same result,

    =IFERROR(VLOOKUP(C15,$B$4:$F$12,MATCH(D15,$D$3:$F$3,0),0),” “)

  • By house painters brisbane, October 17, 2014 @ 10:00 pm

    Thank you, I’ve just been searching for info about this subject for a while and
    yours is the best I have discovered so far. However, what in regards to the bottom line?
    Are you sure concerning the source?

  • By alat bantu sex termurah, November 16, 2014 @ 9:01 pm

    Great beazt ! I wish to apprentice even as you amend your web site, how can i subscribe
    for a blog website? The account aided me a applicable deal.

    I were tiny bit achquainted of this your broadcast provided shiny clear concept

  • By Bogowie online 2014, November 18, 2014 @ 2:30 pm

    Hello, i believe that i saw you visited my website thus i got here to return the desire?.I’m trying to in finding issues to enhance my site!I assume
    its ok to use some of your ideas!!

  • By Neinstein Associates LLP, November 20, 2014 @ 7:06 am

    This is thee right website for everyone whho wants to understand
    this topic. You realize a whole lot its almost hard to argue with you (not
    that I actually would want to…HaHa). Yoou definitely put a brqnd new spin oon a topic that’s been written abnout for a
    long time. Excellent stuff, just wonderful!

  • By Michele, November 30, 2014 @ 1:43 am

    Nice weblog right here! Additionally your site loads up fast!
    What wweb host are you the use of? Can I am getting your affiliate hyperlink on your host?
    I want my web site loaded up as fast as ours
    lol

  • By rabotavmeste.com, December 4, 2014 @ 6:58 pm

    It’s awesome to go to see this site and
    reading the views of all friends on the topic of this article, while I
    am also zealous of getting know-how.

  • By amg, December 7, 2014 @ 9:50 am

    If you are going for most excellent contents like myself, simply pay a quick visit this site everyday as it gives quality contents,
    thanks

  • By ibps Specialist officer 2015 notification, December 24, 2014 @ 12:04 am

    They do not perceive sounds at ɑll even witҺ amplified speech.

  • By Franklyn, December 24, 2014 @ 8:41 am

    That is really interesting, You’re an excessively skilled blogger.

    I have joined your feed and sit up for in quest of more of your
    great post. Additionally, I have shared your web site in my
    social networks

  • By convergys jobs philippines, December 26, 2014 @ 9:20 pm

    I’ve been exploring for a bit for any high-quality articles or weblog posts on this kind of house .
    Exploring in Yahoo I eventually stumbled upon this site.
    Reading this info So i’m happy to express that I’ve a very just right uncanny feeling I discovered just what I needed.

    I such a lot definitely will make sure to do not
    put out of your mind this website and provides it a look on a constant basis.

  • By Gerard, December 31, 2014 @ 1:31 am

    For the web site creator, may I question how should you deal with spam?

    We’re wanting to invest mainly for suggestions to address it!

  • By magic flight, January 4, 2015 @ 8:17 am

    Hi there all, here every person is sharing such know-how, therefore it’s good to read this webpage, and
    I used to pay a visit this weblog all the time.

  • By best car amp for the money 2013, January 30, 2015 @ 4:21 am

    Choose a car amplifier that has audio level inputs which are
    capable of processing increased signals.

  • By Mauricio, February 4, 2015 @ 2:02 am

    This is ann excellent option foor mothers who cannot commit
    to a full-time job. thee more prices you will charge from people,the more profit you wipl
    get. The biggest adjustment has to do with the way you eat.

  • By musique pour une soiree Drummondville, February 11, 2015 @ 7:42 pm

    I like the helpful info you supply in your articles. I’ll bookmark your blog and test
    once more right here frequently. I’m fairly sure I’ll learn many new stuff right right
    here! Good luck for the following!

Other Links to this Post

RSS feed for comments on this post. TrackBack URI

Leave a comment