SOLVED

use VLOOKUP without mentioning table

%3CLINGO-SUB%20id%3D%22lingo-sub-3103511%22%20slang%3D%22en-US%22%3Euse%20VLOOKUP%20without%20mentioning%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3103511%22%20slang%3D%22en-US%22%3E%3CP%3EHallo%20all%2C%3C%2FP%3E%3CP%3EIm%20having%20some%20difficuliets%20with%20the%20VLOOKUP%20function.%20I%20would%20like%20to%20type%20the%20name%20of%20the%20product%20and%20have%20the%20price%20show%20up%20next%20to%20it.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22jesseisunavailable_0-1643791091299.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F344472i4DB4F38663E8CE07%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22jesseisunavailable_0-1643791091299.png%22%20alt%3D%22jesseisunavailable_0-1643791091299.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EFirst%20of%20all%20I%20made%20a%20list%20and%20gave%20each%20section%20its%20own%20table.%20For%20example%20Tomato%20has%20the%20table%3A%20Veggies%20%26amp%3B%20Eggs%20has%20the%20table%3A%20Diary.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22jesseisunavailable_1-1643791091296.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F344474i4A3A2AD9CA6A95D0%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22jesseisunavailable_1-1643791091296.png%22%20alt%3D%22jesseisunavailable_1-1643791091296.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20I%20use%3A%20%3DVLOOKUP(Eggs%2CDiary%2CFALSE)*Amount%3C%2FP%3E%3CP%3EThis%20works%20fine%2C%20but%20I%20would%20like%20to%20do%20it%20without%20manually%20typing%20the%20table%20name%20into%20the%20formula%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22jesseisunavailable_2-1643791091337.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F344473iD20DD692B75C558F%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22jesseisunavailable_2-1643791091337.png%22%20alt%3D%22jesseisunavailable_2-1643791091337.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20also%20tried%20to%20take%20the%20table%20name%20out%20of%20the%20formula%20by%20putting%20it%20next%20to%20it.%20This%20also%20didnt%20work%2C%20I%20got%20the%20error%26nbsp%3B%23N%2FA.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20make%20this%20so%20the%20formula%20searches%20in%20every%20table%20for%20the%20right%20word%2C%20instead%20of%20having%20to%20put%20the%20table%20name%20in%20the%20formula%20manually%20each%20in%20time%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3103511%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20Scripts%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3103747%22%20slang%3D%22en-US%22%3ERe%3A%20use%20VLOOKUP%20without%20mentioning%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3103747%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1294446%22%20target%3D%22_blank%22%3E%40jesseisunavailable%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUse%20INDIRECT%3A%3C%2FP%3E%0A%3CP%3E%3DVLOOKUP(C4%2CINDIRECT(B4)%2C2%2CFALSE)*D4%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3103799%22%20slang%3D%22en-US%22%3ERe%3A%20use%20VLOOKUP%20without%20mentioning%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3103799%22%20slang%3D%22en-US%22%3EThank%20you!%20This%20worked.%3CBR%20%2F%3EDo%20you%20know%20if%20its%20possible%20to%20not%20mention%20the%20tabble%20array%20at%20all%3F%20Or%20make%20VLOOKUP%20search%20in%20all%20the%20table%20arrays%3F%20For%20example%20%3DVLOOKUP(C4%2CDiary%3BVeggies%3BCarbs%2C2%2CFALSE)*D4%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3103830%22%20slang%3D%22en-US%22%3ERe%3A%20use%20VLOOKUP%20without%20mentioning%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3103830%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1294446%22%20target%3D%22_blank%22%3E%40jesseisunavailable%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20create%20just%20one%20long%20list%20with%20two%20columns.%20You%20can%20then%20use%20a%20simple%20VLOOKUP%20on%20the%20single%20list.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hallo all,

Im having some difficuliets with the VLOOKUP function. I would like to type the name of the product and have the price show up next to it.

jesseisunavailable_0-1643791091299.png

First of all I made a list and gave each section its own table. For example Tomato has the table: Veggies & Eggs has the table: Diary.

jesseisunavailable_1-1643791091296.png

 

Here I use: =VLOOKUP(Eggs,Diary,FALSE)*Amount

This works fine, but I would like to do it without manually typing the table name into the formula

jesseisunavailable_2-1643791091337.png

I also tried to take the table name out of the formula by putting it next to it. This also didnt work, I got the error #N/A.

 

How can I make this so the formula searches in every table for the right word, instead of having to put the table name in the formula manually each in time?

 

Thank you

 

4 Replies
best response confirmed by jesseisunavailable (New Contributor)
Solution

@jesseisunavailable 

Use INDIRECT:

=VLOOKUP(C4,INDIRECT(B4),2,FALSE)*D4

Thank you! This worked.
Do you know if its possible to not mention the tabble array at all? Or make VLOOKUP search in all the table arrays? For example =VLOOKUP(C4,Diary;Veggies;Carbs,2,FALSE)*D4

@jesseisunavailable 

I'd create just one long list with two columns. You can then use a simple VLOOKUP on the single list.

I found out how to do it with 3 tabbles. You have to combine the VLOOKUP function with the IFERROR function. So if it can't find the name in the table, it will go to the next one, like this: https://i.imgur.com/oZ0DkNS.png