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!]


8 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

Other Links to this Post

RSS feed for comments on this post. TrackBack URI

Leave a comment