LOOKUP function where value is more than one time

[inddon]

Hello There,

In my worksheet a lookup value appears more than one time (in my case max 2 times) in 2 different rows. I want to derive its values based on row wise. The first row is simple, however when the lookup value appears in another row, I don't know how to get its corresponding values using formula

I have included a sample worksheet for your reference.

Could you please advice.

Many thanks
Don

 

[Naresh Yadav]

Try this one based on your given condition
finish the formula with pressing CTRL+SHIFT ENTER

=IFERROR(INDEX($D$4:$D$9,SMALL(IF($B$4:$B$9=$B15,ROW($D$4:$D$9)-3),COLUMN(A1))),"")

 

[inddon]

Try this one based on your given condition
finish the formula with pressing CTRL+SHIFT ENTER

=IFERROR(INDEX($D$4:$D$9,SMALL(IF($B$4:$B$9=$B15,ROW($D$4:$D$9)-3),COLUMN(A1))),"")

Hi Naresh,

Thank you for your prompt reply.

I have created a Define Range= RangeName.

Infact, in the actual worksheet there are multiple columns. The output needs to be in another worksheet.

How will this work with the above? Could you please advice.

Thanks
Don

 

[Naresh Yadav]

You have to make some changes in formula as highlighted in BOLD

=IFERROR(INDEX(RangeName,SMALL(IF($B$4:$B$9=$B15,ROW($B$4:$B$9)-2),COLUMN(A1)),3),"")

 

[Villalobos]

Hi,

Here is an other approach.

 

[inddon]

You have to make some changes in formula as highlighted in BOLD

=IFERROR(INDEX(RangeName,SMALL(IF($B$4:$B$9=$B15,ROW($B$4:$B$9)-2),COLUMN(A1)),3),"")

Hi Naresh,

Thank you again.

I am doing something wrong with the formula. Could you please check the attached workbook. There are 2 worksheets 'Original' and 'Result'

2 Named Ranges defined for Worksheet 'Original':
RangeData = whole worksheet
RangeNumber = Column A

I have included your formula in Worksheet: Result, ColumnB2

Many thanks and look forward to hearing from you.
Don

 

[Naresh Yadav]

Hi,

pls let me know the logic behind giving named range if you can get your result without this..

 

[inddon]

Hi,

pls let me know the logic behind giving named range if you can get your result without this..

Hi Naresh,

Depending on the data, the number of rows will fluctuate. Also the worksheet 'original', there are too many columns.

The main purpose for defining named ranges is for ease and understanding.

Hope with this, it might be of help to other users as well.

Appreciate your help.

Look forward to hearing from you.

Regards,
Don

 

[Naresh Yadav]

pls see if this is ok for you
i have created the name range upto 100 rows that you can increased based on your requirement

originalnumber =Original!$A$2:$A$100
originalresultarea =Original!$D$2:$I$100
resultnumber =Result!$A$2:$A$100

 

[inddon]

pls see if this is ok for you
i have created the name range upto 100 rows that you can increased based on your requirement

originalnumber =Original!$A$2:$A$100
originalresultarea =Original!$D$2:$I$100
resultnumber =Result!$A$2:$A$100

Hi Naresh,

Thanks for the formula, it looks good.

I was trying to study your formula and play around a bit, but could not understand. It works perfect.


I tried to do the following:
1. Add one more columns Sr No.
2. Extend the Named Range (originalresultarea =Original!$B$2:$I$100)

In the worksheet 'Result' the values under their column headings changes to a different value.

I have attached the sample workbook


How can this be made flexible?
If you could please explain the working of the formula, just for my understanding.


Many thanks and regards,
Don