• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VlookUp // Can i find the Column index no. from Diffrent row.

R

riya

Member
Hi All,
I have the attached data where i have to find the specific value by lookup, but problem is that lookup value and column index no. is in different row. i have tried from Vlookup function but no luck. Could you please assist or have to use another function.

Thank you.
 

Attachments

Did you try with the INDEX () function. It uses a column and row index number to fetch data. Combine if with match like this:
=INDEX(array,row_num,column_num)
-> replace row_num and Column_num arguments with: match(lookup_value,lookup_array,match_type)

Pay attention your data must be organized in a way to make this work (ordered ascending, descending). I did not look at your data. Unable to download for the moment.
 
GraH - Guido said:
Did you try with the INDEX () function. It uses a column and row index number to fetch data. Combine if with match like this:
=INDEX(array,row_num,column_num)
-> replace row_num and Column_num arguments with: match(lookup_value,lookup_array,match_type)

Pay attention your data must be organized in a way to make this work (ordered ascending, descending). I did not look at your data. Unable to download for the moment.
Click to expand...

Hi.. I have also tried INDEX/MATCH but not getting the output. If you get time please look the file. Thank you
 

Attachments

riya
That is 'Excel-function'; 'user-made-function' = like 'Match-Match'.
Maybe someone else would do it with other way - not me.
 
riya said:
Hi GraH, Yes i have seen your file. But i can not make any change in the original data file. i have to apply functions only on original file.
Click to expand...
Riya, I believe you, but I find it hard to understand you cannot change your own file format. Even if you receive this file from an external source, you could ask to that owner to make a minor change. Also Marc L. does a similar suggestion to organize the data better for what you want to accomplish. His suggestion if even smarter: get the service number repeated in the total row. You then simply fetch that result via multiple options.
But if really impossible,... perhaps this:
AGGREGATE(14,6,($I$3:$I$21)/($F$3:$F$21="FINAL"),COUNTIF(INDIRECT("F3:F"&MATCH(A2,F:F,0)),"FINAL"))
 

Attachments

Last edited:
You must log in or register to reply here.
Back
Top