Forum Discussion

katie's avatar
katie
Copper Contributor
Sep 01, 2018

Match Cells on Sheet 1 & 2 and return value from a cell on Sheet 2 to a new cell on Sheet 1

Sheet 1, Column B lists names. Same names are listed multiple times. Sheet 2, Column A lists one name and Sheet 2, Column B lists phone # for corresponding name.   If the name in Sheet 1, Column B ...
excel
Formulas and Functions
Macros and VBA
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 10, 2021

HMills8475 

MATCH(J1, Sheet2!G:G, 0) returns first found position in Sheet2!G:G where the value is equal to value of J1, or error if nothing was found.

INDEX(Sheet2!E:E, <above position>) returns the value of the cell in column Sheet2!E:E which is on that position.

All together

=IFERROR( INDEX ( Sheet2!E:E, MATCH( J1, Sheet2!G:G, 0) ), "nothing found")
HMills8475's avatar
HMills8475
Copper Contributor
Sep 13, 2021

SergeiBaklan Thank you!!! I could literally cry, I'm so happy it worked Bless you!! 

  • Jimmy07071's avatar
    Jimmy07071
    Copper Contributor
    Jul 10, 2023

    I am so lost. I have a workbook with two tabs. First Tab is names Orders. In this tab, column "D" has order numbers. I need to check these numbers against tab #2 named DR. There is only one column in this tab. There is a text string of a description of the order AND the order number. I need to find the data od tab 1 column D in tab 2 and if its there, I need to know. Can anyone please help?

     

     

    • selvamohanchinnasamy's avatar
      selvamohanchinnasamy
      Copper Contributor
      Sep 30, 2021

      SergeiBaklan I have a requirement like - I need to check the values of 2 different cells in Sheet 1 with master data in sheet 2 and if they match, then I need to display the $ values from master data on a cell in sheet 1. If (Sheet1.Resource Location.value AND Sheet1.Resource Type.value) = (Sheet2.Resource Location.value AND Sheet2.Resource Type.value) then sheet1.Hourly Rate.value =Sheet2.HourlyRate.value (This should be checked across the master values to see if the shee1 data combination has an entry to return the $rate).

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Sep 30, 2021

        selvamohanchinnasamy 

        Depends on your Excel version you may use

        =XLOOKUP(A2&B2,Sheet2!$A$2:$A$46&Sheet2!$B$2:$B$46,Sheet2!$C$2:$C$46,"no such")

or

=IFERROR( INDEX( Sheet2!$C$2:$C$46, MATCH( A2&B2, Sheet2!$A$2:$A$46&Sheet2!$B$2:$B$46,0 ) ), "no such")

        Much better if you transform source data into structured tables (Ctrl+L), with that formulas could be

        =XLOOKUP(
  [@[Resource Location]] & [@[Resource Type]],
  Table2[Resource Location] & Table2[Resource Type],
  Table2[Hourly Rate],"no such")

        or

        =IFERROR(
   INDEX( Table2[Hourly Rate],
          MATCH(
             [@[Resource Location]] & [@[Resource Type]],
             Table2[Resource Location] & Table2[Resource Type],
             0 )
    ),
 "no such")