Connecting few tables, each in different sheet. I need modified vlookup function.

Hi there.

Here is the situation.

I have table like this in sheet no. 1
-------------------------------------------------
our | supplier | supplier
code | name | code
111000 Marko 902
121000 Marko 963
432000 Marko 952
232000 Marko 976
111000 Marko 965
121000 Marko 911
432000 Pero 10576
232000 Pero 10883
111000 Pero 10923
121000 Pero 10221
432000 Pero 10664
232000 Pero 10769


2nd table is in the sheet no. 2
-------------------------------------------
our | supplier | supplier
code | name | code
111000 Marko + THIS IS WHERE I NEED TO INSERT FUNCTION
121000 Marko
432000 Marko
232000 Marko
111000 Marko
121000 Marko
432000 Pero
232000 Pero
111000 Pero
121000 Pero
432000 Pero
232000 Pero
----------------------------------------------------------------------------------------------------------------------------------
Function needs to return value of the supplier code from the table in sheet 1 here in sheet 2, using our code and supplier name.

vlookup function is most similar to what i need but it returns 1st supplier name according to our code. Please notice that in our code list there are same codes but the supplier name is different. (green font as example)

In short words, I needs some function or macro that will return supplier code according to our code and supplier name. Problem with the vlookup function is that returns supplier code of the 1st our code that founds. It don't look to supplier name column.

If I haven't been clear enough please say it, I'll explain in more details.

Ty all in advance.
Dean

Hi Dean,

In your example, row 1 and row 5 have the same "our code" (111000), and same "supplier name" (Marko), but the supplier code is 902 and 965. How do you determine which one you want?

Cheers,

Hi;

Its a mistake. It was hard to post text so it looks like a sheet in excel

The point is that i have the same product from 2 different suppliers (Marko and Pera).
Lets say that I used 111000 as a code for that product. Supplier codes are different. Marko uses lets say code 902 for that product and Pera uses 965.

When Im making an order, I only order from one of those suppliers, but suppliers require their product code, and not mine. When I use vlookup function it searches the table and when it runs on to 111000 code it stops and gives me Markos code as hes the first in the column. Even if on the second field is Pera it gives me Markos code again. It dont include other column (supplier name) into the math...

I hope this clears it.
ty

Okay, give this a try:

Please Login or Register  to view this content.

use with Ctrl-Shift-Enter (CSE), and adjust ranges to suit.

I went this route in case you have alphanumeric supplier codes.

Cheers,

Ty for taking interest in my case mate.
I tried to apply this function but it doesn't work. I get #VALUE error.

I made a new example of tables so I'll attach the xls file.
It seems that formula stuck at the step 2 of the calculation.

Im sure that solution for this problem is using INDEX function and then MATCH in its body but cant figure out good syntax. Can you help me understand what INDEX part does and what MATCH part in the formula does exactly?

ty, Dean

can you attach an example sheet

Hi,

You need to use the Ctrl-Shift-Enter with my formula. Please see the example:

Cheers,

It's how Excel uses array formulas.