excel index function implementation needed

0
votes

in the following formula,

i need to search for each row in sheet one, all rows in sheet2, in columns B and D, and get from there the corresponding values:

=X2&"|" & IF(AND(Sheet1!E2=Sheet2!B2,Sheet1!C2=Sheet2!D2),Sheet2!Z2 & "|" & Sheet2!Y2,"")

=X2&"|" & IF(AND(Sheet1!E2=Sheet2!B:B,Sheet1!C2=Sheet2!D:D),Sheet2!Z#INDEX_of_lineWHICHMATCHESCRITERIA & "|" & Sheet2!Y#INDEX_of_lineWHICHMATCHESCRITERIA,"")

I have not figured how to properly use the INDEX function to achieve this, combined with the above formula

i put the task in detail: I have an excel file. The task needed to be accomplished, is the following: for each row (actually for each id, stored in column A - which exists in sheet 1 ) that the following condition is true:

column E (which exists in sheet 1) = column B (which exists in sheet 2) AND column C (which exists in sheet 1) = column D (which exists in sheet 2),

i must insert data from column Z (which exists in sheet 2) and column Y (which exists in sheet 2) to column X (which exists in sheet 1).

If column X, has already data in it, all new inserted data must have | as a separator, and should be inserted in front of the existing value.

If no data exist in column Z, or Y, then nothing is inserted to column X.

I have tried without success, to use index match, but no luck, i think something more complicated is needed. How can i do this with an excel formula? How the below formula should be transformed, in order to get the row that fulfils the conditions? =X2&"|" & IF(AND(Sheet1!E2=Sheet2!B2,Sheet1!C2=Sheet2!D2),Sheet2!Z2 & "|" & Sheet2!Y2,"")

=X2&"|" & IF(AND(Sheet1!E2=Sheet2!B:B,Sheet1!C2=Sheet2!D:D),Sheet2!Z#lineWHICHMATCHESCRITERIA & "|" & Sheet2!Y#lineWHICHMATCHESCRITERIA,"")

1
excelexcel-formula
=INDEX(Sheet2!$B:$B, MATCH(criteria, Sheet2!$B:$B, 0)) where criteria is the value you are trying to match. – Tim Wilkinson
what confuses me, is that i must match row 2 from sheet 1, with another row from sheet2, and the criteria is E2=value in columnB AND value from C2 -value from the corresponding row from column D, how should i make the above formula in order to get the values i want? – Rudolf
You can match two values as per the above by defining two look up values, for example {=MATCH(Sheet1!$E2&Sheet1!$C2,Sheet2!$B:$B&Sheet2!$D:$D, 0)} just remember to enter with CTRL+SHFT+ENTER – Tim Wilkinson
=X2&"|" & IF(AND(Sheet1!E2=Sheet2!B2,Sheet1!C2=Sheet2!D2),Sheet2!Z2 & "|" & Sheet2!Y2,"") for the condition met AND Sheet1!E2=Sheet2!B2,Sheet1!C2=Sheet2!D2, i need to extract value from column Z and Y, still confused how i should combine your suggestion with my formula... – Rudolf
Me too, put some sample data up. In your comment above it says IF A = B AND IF A = B, why check the same thing twice? You need to read through Boris' answer below, and then search for INDEX MATCH Multiple Values. – Tim Wilkinson

1 Answers

1
votes

Hope this helps you grasp the logic behind INDEX and MATCH:

INDEX(array, row_num, [column_num]) - returns value from cell.

  • array - range of cells that you want the result from.
  • row_num - number of the row that you want result from.
  • column_num - number of the column that you want result from.

Note: Row / column number is relative to selected range.

Examples:

  • INDEX(A1:A5,3) will return value from A3
  • INDEX(A5:A10,3) will return value from A8
  • INDEX(A1:G1,3) will return value from C1

MATCH(lookup_value, lookup_array, [match_type]) - returns row / column of first match.

Note: returned row / column number is relative to selected range.

  • lookup_value - this is the number or text you are looking for and it can be a value or a cell reference.
  • lookup_array - range of cells in which you want to search for the value.
  • match_type - this is a parameter that tells formula should it return exact match or nearest value.

Example:

  • MATCH("Cat",A1:A5,0) will return row number of cell that has value "Cat"

Now, if you combine those two it will look like this:

INDEX (column to return a value from, (MATCH (lookup value, column to search from, 0))

  • column to return a value from - range of cells that you want the result from.
  • lookup value - value for which you would like to find corresponding data.
  • column to search from - range of cells in which you want to search for the value.

Example:

  • INDEX(B1:B5,MATCH("Cat",A1:A5,0) will find Cat in column A and return value from the same row of column B.