SOLVED

use VLOOKUP without mentioning table

Copper 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 (Copper 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
1 best response

Accepted Solutions
best response confirmed by jesseisunavailable (Copper Contributor)
Solution

@jesseisunavailable 

Use INDIRECT:

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

View solution in original post