How to add multiple functions to 1 cell.

[jeroenheki]

Hi,
My question is explained in the attachment.

Example 1.xlsx

[carrob]

See the attachment and let me know if this is what you were looking for.

[jeroenheki]

Yes Carrob this was what i'm looking for!

Although you had 1 little type in the cell.
You had "=VLOOKUP(VLOOKUP(B9; Table2[[#All];[I-Number]:[C-number]];2); Table3;2)"
But it should've been "=VLOOKUP(VLOOKUP(B9; Table2[[#All];[I-Number]:[C-number]];1); Table3;2)"

Thanks anyways carrob!

[carrob]

happy to help

[jeroenheki]

Hey Carrob,

can you explain this formula to me? I need to add it in an other document, but there the locations of the cells and tables are different.

[carrob]

vlookup works by searching the left most column of a table for a value, then going straight across to a specified column in that table and grabbing the value from that cell. In this case since you have two tables a vlookup within a vlookup is required.

Here's the formula you used:
=VLOOKUP(VLOOKUP(B9; Table2[[#All];[I-Number]:[C-number]];2); Table3;2)

Starting with the red text:

This vlookup will look at whatever value is in cell B9 on Sheet 1 (wwhich is your "I-Number"). The second value is the table you want to lookup values from. In this case it will go to table 2 on your Sheet 3 and search the first column for that value. Vlookup ALWAYS searches the first column of the table you select for that value. So if the value of cell B9 is 3, vlookup will see that, then go to table 2 and find that the value 3 is in cell A4. The third and final value is the column number you want to pull data from. So vlookup already knows the value 3 is in row 4 of that sheet. The final value provided is 2, which means to grab the value from the 2nd column in the table/array you already selected. Since the value we grabbed from B9 is in the first column in that table, it just goes over one column to the "C-number" and grabs that value, in this case it is 2.

=VLOOKUP(VLOOKUP(B9; Table2[[#All];[I-Number]:[C-number]];1); Table3;2)

Now look at the red text above:

This works the same way, so the value it will search table 3 for is the value we just got from the first vlookup: 2. It will find the value 2 on your customers sheet in row 3. Since the column number you selected is 2 it will just move over one cell and grab that value, in this case "John Doe" will be the final output value of the formula.

In summary, this is how you write a vlookup equation:

=vlookup(VALUE YOU ARE SEARCHING, TABLE YOU ARE SEARCHING IN, COLUMN NUMBER TO LOOK IN)

so as one last example:

=VLOOKUP(4, Table2[#All], 2) would return the value 1.

It searches table 2 on sheet 3 for the value 4 in the I-number column. It then goes to the 2nd column in the table, your c-number column, and grabs that value.

I tried to explain in as much detail as possible, let me know if this is too confusing.

[jeroenheki]

I'm sorry for the late reply, but i need to thank you Carrob.
It took some time to puzzle it out, but it worked now and i have to give you a big thanks

Keep up the good work Carrob!