-1

this is my first question here. I'm looking for a good way for finding values in table, if other values in the same row exist. For example, my table looks like this.

enter image description here

That's what I'm trying to do:

  1. Look for specific row using index value (probably vlookup?).
  2. Check if data3 exist in that row (probably hlookup?).
  3. If so return value from next cell.
  4. If not return value from cell next to data_def.
  • If your table consists of only those many columns A thru I you can warp VLOOKUP in nested IF to check for 'data3' and after 3 iterations if not found pull the value from column C – patkim May 12 '17 at 18:58
  • Unfortunately this is only an example. My real table (which I can't post here) consists about 70 columns. Nesting IF's isn't really an option here. – Piotr May 12 '17 at 19:19
  • What have you tried so far? I see you reference some lookups, have you tried one? Maybe with OFFSET() too? Or Index/Match? – BruceWayne May 12 '17 at 20:30
0

You could nest several Index functions to do this.

First, establish the row of the desired value in column A with these two Index functions:

=INDEX(A:A,MATCH(FindIndex,A:A,0)):INDEX(Z:Z,MATCH(FindIndex,A:A,0))

The FindIndex is a named cell (C7) that has the value you want to find in column A. If you have 70 columns of data, you need to adjust the Z:Z to be the last column of your data.

Now you can use this range inside another Index/Match combo, where it serves as the range for Index, and again as the range for Match. In the Match part, the findData is a range name for a cell (C8) that holds the "data3" value. Match finds that cell by adding 1 to the result, you can return the next cell over.

    =INDEX(   
    INDEX(A:A,MATCH(FindIndex,A:A,0)):INDEX(Z:Z,MATCH(FindIndex,A:A,0)),
  MATCH(findData,INDEX(A:A,MATCH(FindIndex,A:A,0)):INDEX(Z:Z,MATCH(FindIndex,A:A,0)),0)+1)

enter image description here

  • Thank you, but I have problems with implementing this, when my data is in one sheet, and "search box" cells (velue to find etc) are in another. How can I dynamically reference row in other sheet using this method? – Piotr May 14 '17 at 19:49
  • Well, if your data is on another sheet, just use the sheet name in the cell references. So A:A will become Sheet2!A:A. If your sheet name has spaces it must be in single quotes like this: 'My Data Sheet'!A:A. Excel will help you build the correct reference if you just type a = sign, then click the other sheet and select a cell and hit enter. Now the formula bar has the correct reference. Or, to quickly use the formula I provided, paste it into a cell, then select the A:A bit and click to the column in the other sheet that has the data. – teylyn May 15 '17 at 1:01
  • Ok, I finally implemented this, but for some reason it searches and return the value from one row below I want. Maybe some things was lost in translation, since I'm not using English version of Excel. – Piotr May 15 '17 at 4:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.