• 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

  • vlookup.xlsx
    9.5 KB · Views: 5
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

  • vlookup.xlsx
    9.5 KB · Views: 1
Riya,
Would it make sense to you, to repeat the service number and use a SUMIF?
=SUMIF(F:F,A2,I:I)
 

Attachments

  • vlookup.xlsx
    9.9 KB · Views: 2
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

  • vlookup.xlsx
    9.9 KB · Views: 6
Last edited:
You must log in or register to reply here.
Back
Top