Reverse HLOOKUP [SOLVED]
- Thread starter sujit_24
- Start date
SirJB7
Excel Rōnin
Hi, sujit_24!
If so you might do this:
a) Use a helper row, 3 for example:
A3: =CONTAR.SI($A1:A1;A1) -----> in english: =COUNTIF($A1:A1,A1)
and copy across thru F3.
b) Use this array formula:
=INDICE(A2:F2;1;COINCIDIR("Raj"&"_"&CONTAR.SI(A1:F1;"Raj");"Raj"&"_"&A3:F3;0)) -----> in english: =INDEX(A2:F2,1,MATCH("Raj"&"_"&COUNTIF(A1:F1,"Raj"),"Raj"&"_"&A3:F3,0))
Remember that array formulas should be entered with Ctrl-Shift-Enter instead of just Enter.
Just advise if any issue.
Regards!
If so you might do this:
a) Use a helper row, 3 for example:
A3: =CONTAR.SI($A1:A1;A1) -----> in english: =COUNTIF($A1:A1,A1)
and copy across thru F3.
b) Use this array formula:
=INDICE(A2:F2;1;COINCIDIR("Raj"&"_"&CONTAR.SI(A1:F1;"Raj");"Raj"&"_"&A3:F3;0)) -----> in english: =INDEX(A2:F2,1,MATCH("Raj"&"_"&COUNTIF(A1:F1,"Raj"),"Raj"&"_"&A3:F3,0))
Remember that array formulas should be entered with Ctrl-Shift-Enter instead of just Enter.
Just advise if any issue.
Regards!
Hi,
The am looking for a formula which will give me last occurance of string within a range. Also I am trying to use wild character within that formula. Lets say
Aman Raj Akshay Raj Amit Raj Rajsawant Akshay
5 6 7 2 5 7 9 11
Below formula gives output as 6 but I need output as 9.
=HLOOKUP("Ra*",A1:H2,2,0)
In the above formula it should look for string starting with Ra....
And the last occurance in table array.
If you look at above table array Rajsawant is the last occurance of a string starting with Ra and its values is 9. I am looking for formula which will give me output as 9. I have to apply this formula to 120000 cells. So it would be better if formula is not dependent on other cell formula output.
The am looking for a formula which will give me last occurance of string within a range. Also I am trying to use wild character within that formula. Lets say
Aman Raj Akshay Raj Amit Raj Rajsawant Akshay
5 6 7 2 5 7 9 11
Below formula gives output as 6 but I need output as 9.
=HLOOKUP("Ra*",A1:H2,2,0)
In the above formula it should look for string starting with Ra....
And the last occurance in table array.
If you look at above table array Rajsawant is the last occurance of a string starting with Ra and its values is 9. I am looking for formula which will give me output as 9. I have to apply this formula to 120000 cells. So it would be better if formula is not dependent on other cell formula output.
SirJB7
Excel Rōnin
Hi, sujit_24!
We posted simultaneously. Check my suggested formula to see if that's what you want to achieve. But it works only for exact matching strings, not for wildcards use.
Regards!
EDITED
PS: Even if it's suitable for you, for 120K cells you should first give a try as I think it'll have a bad performance because of the array formula.
We posted simultaneously. Check my suggested formula to see if that's what you want to achieve. But it works only for exact matching strings, not for wildcards use.
Regards!
EDITED
PS: Even if it's suitable for you, for 120K cells you should first give a try as I think it'll have a bad performance because of the array formula.
Hi SirJB7,
The formula works
This will not work for me because we are taking helper row to support the formula.
Is there any other way by which I can get this output without helper formula.
Below is my actual data table.
There are different columns Previous Month, Jan, Feb, Mar, Apr ........ Dec
When you see below the data is rendered as {Jan:14,May:5,Aug:7} which comes from database. Here not all cells have value. In Jan 6th row has value, in Feb 1st, 3rd and 7th row have values.
When we look at Jan 1st row which is empty ... actually it should refer to Previous Month to those get value. i.e Jan 1st row will have value {Jan:14,May:5,Aug:7} and Mar 1st row will have values {Jan:14,Apt:3,Aug:17} which will refer to Feb 1st row.
To achieve the above i am using VBA special cell formula, where i will apply formula to blank cells. Mapping to actual reference i.e actual column is the intermediate of formula, on top of it I am altering formuala to get the desired output.
Once I get value as {Jan:14,May:5,Aug:7}, 14 has to be extracted form this string which is the value of Jan
[pre]
[/pre]
The formula works
This will not work for me because we are taking helper row to support the formula.
Is there any other way by which I can get this output without helper formula.
Below is my actual data table.
There are different columns Previous Month, Jan, Feb, Mar, Apr ........ Dec
When you see below the data is rendered as {Jan:14,May:5,Aug:7} which comes from database. Here not all cells have value. In Jan 6th row has value, in Feb 1st, 3rd and 7th row have values.
When we look at Jan 1st row which is empty ... actually it should refer to Previous Month to those get value. i.e Jan 1st row will have value {Jan:14,May:5,Aug:7} and Mar 1st row will have values {Jan:14,Apt:3,Aug:17} which will refer to Feb 1st row.
To achieve the above i am using VBA special cell formula, where i will apply formula to blank cells. Mapping to actual reference i.e actual column is the intermediate of formula, on top of it I am altering formuala to get the desired output.
Once I get value as {Jan:14,May:5,Aug:7}, 14 has to be extracted form this string which is the value of Jan
[pre]
Code:
Previous Month Jan Feb Mar Apr
{Jan:14,May:5,Aug:7} Empty {Jan:14,Apr:3,Aug:17} Empty Empty
{Jan:14,May:5,Aug:7} Empty Empty {Jan:14,May:5,Aug:8} Empty
{Jan:14,May:5,Aug:7} Empty {Jan:14,May:5,Aug:7} Empty Empty
{Jan:12,May:5,Aug:7} Empty Empty Empty Empty
{Jan:14,May:5,Aug:7} Empty Empty Empty Empty
{Jan:14,May:5,Aug:7) {Jan:14,Mar:5,Aug:7} Empty {Jan:14,Mar:5,Aug:7} Empty
{Jan:14,Mar:5,Apr:7} Empty {Jan:14,Mar:5,Jun:7} Empty EmptySirJB7
Excel Rōnin
Hi, sujit_24!
For the time being I don't imagine a direct formula solution without a helper row. Despite of the fact that it wouldn't handle wildcards. So maybe the method require VBA code.
Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.
Regards!
For the time being I don't imagine a direct formula solution without a helper row. Despite of the fact that it wouldn't handle wildcards. So maybe the method require VBA code.
Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.
Regards!
NARAYANK991
Excel Ninja
Hi Sujit ,
I have not really understood your requirement , but let me put down two formulae :
1. If you want to search using wildcards in a range , you can use the following formula :
=INDEX(Values,LARGE(IF(ISERR(SEARCH(substring,Names)),,COLUMN(Names)),1))
entered as an array formula , using CTRL SHIFT ENTER. This is with specific reference to your earlier post , assuming that you have a range of names in row 1 , starting from cell A1 through H1 ; your range of values is in row 2 , again from A2 through H2.
substring is any text string such as Ra , sha ,...
2. If you want to extract the numeric value from the retrieved text string , assuming that the format is as you have shown , you can use :
=MID(text,SEARCH(month,text)+4,IFERROR(SEARCH(",",text,SEARCH(month,text)),SEARCH("}",text,SEARCH(month,text)))-SEARCH(month,text)-4)
Narayan
I have not really understood your requirement , but let me put down two formulae :
1. If you want to search using wildcards in a range , you can use the following formula :
=INDEX(Values,LARGE(IF(ISERR(SEARCH(substring,Names)),,COLUMN(Names)),1))
entered as an array formula , using CTRL SHIFT ENTER. This is with specific reference to your earlier post , assuming that you have a range of names in row 1 , starting from cell A1 through H1 ; your range of values is in row 2 , again from A2 through H2.
substring is any text string such as Ra , sha ,...
2. If you want to extract the numeric value from the retrieved text string , assuming that the format is as you have shown , you can use :
=MID(text,SEARCH(month,text)+4,IFERROR(SEARCH(",",text,SEARCH(month,text)),SEARCH("}",text,SEARCH(month,text)))-SEARCH(month,text)-4)
Narayan
Thanks to all for your support.
NARAYANK991,
Below formula you gave it worked. It was awesome.
=INDEX(Values,LARGE(IF(ISERR(SEARCH(substring,Names)),,COLUMN(Names)),1))
Amritansh,
Formula you gave even that awesome but that did'nt worked for me because it did not support wildcard.
Sujit
NARAYANK991,
Below formula you gave it worked. It was awesome.
=INDEX(Values,LARGE(IF(ISERR(SEARCH(substring,Names)),,COLUMN(Names)),1))
Amritansh,
Formula you gave even that awesome but that did'nt worked for me because it did not support wildcard.
Sujit