Index match

[nfmoreira]

Hello,

I am trying to use formula index match to match in a summary table. I want to add this formula in order to search in another spreadsheet for the value in cell GS 3.
When found, I want to retrieve the value of the cell next to the match.

In the first picture I have the spreadsheet with the summary.

In the second picture I have the spreadsheet where I am going to look for the value.

01.PNG

02.PNG

How can I do this correctly?

Thanks

NM

[nfmoreira]

I forgot to explain that the different values I want to search are not all in the same column or row.
Do I have to do a dynamic search?
How do I perform this?

TY

[Neilesh Kumar]

Request to you kindly do provide Sample workbook with expected results.

[nfmoreira]

I attached a sample workbook.

[Neilesh Kumar]

Hi nfmoreira,

Please find here attached sample workbook with Solution. Is exactly the same you are looking for. Please do let us know.

Please do mark thread as solved if the provided solution furnishes your requirement.

sample.xlsx

[nfmoreira]

In this case it is correct. But the problem is that the value to match in sheet 2 is not always in same column, it varies.
In case of 100 entries of data, the formula you provided would not work because I would have to alter the formula for each entry.

[nfmoreira]

Thanks for your help Neilesh

[Neilesh Kumar]

Hi nfmoreira, Since you have asked for the single match so accordingly i provided. Please find here below the solution based upon the column Headers.

=INDEX(Sheet2!A:FO,MATCH(Sheet1!S3,Sheet2!CI:CI,0),MATCH(U2,Sheet2!A1:FO1,0))

[Neilesh Kumar]

Glad to help. Please mark thread as solved if the provided solution furnished your requirement.

[FDibbins]

nfmoreira welcome to the forum

For future reference, please take a moment to read the forum rules and use thread titles that are descriptive of your problem - not what you think the answer might be. (think google search terms?).

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

[nfmoreira]

Thank you FDibbins.
I will take into acount what you said and be more explicit next time

[nfmoreira]

Hi!
I forgot about the column headers. That's great.
Regarding the first match MATCH(Sheet1!S3,Sheet2!CI:CI,0), that only works if the value I am searching is in that column. How can I make fail safe and make it so that this match searches for the value in all columns?
I tried replacing that part for MATCH(Sheet1!S3;Sheet2!A:FO;0) but it does not work.

[Neilesh Kumar]

Try this nfmoreira,

=INDEX(Sheet2!$A:$FO,MATCH(Sheet1!$S$3,Sheet2!$CI:$CI,0),MATCH(AC2,Sheet2!$A$1:$FO$1,0))

[nfmoreira]

Thanks for helping me in this issue.
I attached an example workbook with one more row of results.
In this case, that formula only works for the first row. The value in the second row of results of sheet 2, is in a different position and with a different header.
How can I make it so that, no matter where the value is, I can find it and retrieve the value of the cell to its right?

[JeteMc]

I imagine that this can be improved on.
1) On sheet2 FL1:FN1 are populated using: =INDIRECT("Sheet1!S"&COLUMN(C:C))
2) FL2:FN10 are populated using: =IFERROR(INDEX($A2:$FJ2,MATCH(FL$1,$A2:$FJ2,0)+1),"")
3) FL11:FN11 are populated using: =SUMPRODUCT((FL2:FL10<>"")*(ROW(2:10)-1))
Note that you could cut and paste cells FL11:FN11 to a row further down (i.e. anywhere below the last possible row of data) of sheet2.
4) On sheet1 the formula that populates U3:U4 is:

Please Login or Register  to view this content.

Let us know if you have any questions.

[FDibbins]

If you intend to continue on this thread, please change the title as I stated in post #10