Share via

SHEET INTERCONNECTED FORMULAS IN EXCEL

Anonymous
2016-05-13T11:10:35+00:00

Hi all,

Please help me with this issue I am trying to solve, I have one sheet with over 10.000 rows of articles and prices, currencies etc... (let's say sheet A),  and I need to check if part of the context in another sheet B, matches part of the context in any cell in sheet A and if it does, copy the price. I would be happy to use the LOOKUP formula (as I do in sheet A), but it seems like it works only within the same sheet, is there any solution?? 

Thanks!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

13 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-05-14T00:36:01+00:00

    Sheet 1: the numbers before the first space in column A should be compared with column A in sheet 2. When the cell value from sheet 2 matches; the result should be the price from column E in sheet 2 and copied in column D in sheet 1.

    Was this answer helpful?

    0 comments
  2. Ashish Mathur 101.9K Reputation points Volunteer Moderator
    2016-05-13T23:36:05+00:00

    Hi,

    Please take some examples to illustrate.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2016-05-13T19:22:18+00:00

    That works only if column A sheet 1 is not formatted as number, and if that is not the case, it doesn't work. And unfortunately I do have mixed context...can you please help me?? for example I need to find if ONLY the part 1MS5432 from A3 cell "1MS5432 cap" in sheet 1 can be found in any cell from sheet 2 and then return the value from column G sheet 2 that corresponds to the found cell.

    Or more detailed:

    When I have mixed context this formula works =LOOKUP(A:A,prices!A:A,prices!G:G) but it doesn't if the part I am searching contains only numbers (without the MS for example)

    When there is only a number and I split the cell, format it as number, then this formula works =SUMPRODUCT(--(ISNUMBER(SEARCH(prices!A:A,A77))),prices!G:G)

    I need universal one, for both cases, is that possible??

    THANKS!

    Was this answer helpful?

    0 comments
  4. Anonymous
    2016-05-13T13:08:47+00:00

    Thanks Ranjeet, but I don't want to enter any information. I only want, if possible, to compare part of the cells in column A from sheet 1 with part of the cells in column A of sheet 2, and if there is a match, get the price from column G in sheet 2 that corresponds to cell in that raw in sheet 2. 

    For example, I have "NUMBER description" in column A in sheet 1, and I have "NUMBER" in column A in sheet 2 (where in column D I have the price); so in sheet 1 I need to create a formula in column B that will check if part of the cell in column A, sheet 1 corresponds to any cell in column A sheet 2, and if so, returns the price.

    Can you please help me with this?

    Was this answer helpful?

    0 comments
  5. Anonymous
    2016-05-13T11:20:03+00:00

    Hi ZoricaK,

    Considering you have  a list of articles in sheet A from column A to column D, and similarly you have some data on sheet B, then instead of using LOOKUP, you should use VLOOKUP Function.

    considering you have the common data which you want to lookup is into column A. and row 1 has the headers, so just fill this function on sheet A, in cell E2 (or you can change as per your requirement)

    =VLOOKUP(A2,Sheet2!$A:$D,4,0)

    you can change the 4 into the above formula to get the values from different columns from sheet B.

    Was this answer helpful?

    0 comments