[solved] 3-way lookup in excel

  • anyone know how to do this? I want to lookup say, B1, B2 & A2, needing all three to show up w/in one row of a range of rows/columns and returning a specified cell for the answer if all are found. Basically I need to do a vlookup but have 3 'lookup_values' If you need more detailed info I'll gladly provide! Thanks!!

  • Hi, and welcome to the forum.

    If you could post an example you'll probably get the answer you need much more quickly. It's a bit hard to tell details from your original post.

    For example, are the three cells you are checking always in the same order (e.g. column A then B then C in the same row)? If so, you possibly could concatenate the cell contents for the involved columns (cell order0 into a composite VLOOKUP table. If you are looking for a specific set of values in any 3 of a larger set of cells and in any order, then a much different solution would be required.

  • It appears that the pivot table wizard will do what you want.

    Have a look in the Excel help for pivot tables and poke around a bit. It is not too dificult ot get the results you are after.
    If you have any questions, post them back on the board.



  • yes a pivot table does give me the info I want.. but not where I want it... I need to put it in this specific spot of the 2nd spreadsheet.. So I will be able to filter the pivot table to show the data I need, but I still need to pull it over to the second sheet!??!

  • I don't understand.
    The pivot table wizard allows you to place the pivot table in another spreadsheet in a specified postion.
    It also allows you to filter the data or change the view of the data.
    Could you explain a bit more detail perhaps.


  • Attached is a Pivot table using your data which can be updated if new data is entered the next day. This pivot table can be refreshed each day without making a new table each day. Please note that I am a bit confussed, because your final sheet does not show the data for UT on the correct date, this should be the 7th through the 12th as shown in the Pivot table. The Pivot table will not let you make a mistake when looking at the data.

    Hope this helps, if you need additional help, let us know.


  • yes sorry about that date thing.. that is why I like functions etc.. so I don't make input errors.. i will take a look at the way you have your pivot table and let you konw how it works! Thanks for the tips!

  • If you really want to do it with equations rather than a pivot table, the SUMPRODUCT function will give you what I think you are after. See the attached -- I changed some of your data (CR and UT) to vary the results a bit.

    Look at the equations in cells K21:L32. The one in K21 is shown in text to the left of the final table.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!