Returning a certain number based on the position of a value

[jasonleewkd]

Hi Chandoo experts, I would like to seek help on the above.


Let me explain

in Sheet 2, A2 has the text "A.C.T Holdings..."

in Sheet 1, A1:BBO134 has a whole bunch of data including "A.C.T Holdings..." in cell EN116; EN1 has text in it "bidder 4"


What I need Sheet 2, cell B2 to return is the numeral "4"


Index, match cannot be used because the problem is slightly more complex. If you could please see the attached I will be grateful.


https://docs.google.com/open?id=0BzQyFnW9qIEXSDVLekRFc1ZiZ3c

 

[Hui]

Jasonleewkd


The value in Populate This A2 doesn't match the value in Data EN116 ?

 

[jasonleewkd]

Apologies Hui.


Here's the spreadsheet with the data cleaned up. https://docs.google.com/open?id=0BzQyFnW9qIEXU3lMZ3pFMVBQTHM


Let me describe my problem.


I want B2 in "Populate this" to return a value of "4", or "bidder number 4", or "4th place".. anything that indicates "A.C.T Holdings..." got 4th place in the bidding process.


I'm thinking a control-shift-enter array formula might work? I've tried index matches, vlookup but just can't seem to find the killer combination of formulas or the finishing touch


Cell B2 currently has if A2 = any data in the range A1:BBO134, then return Y, but I'm getting n. Not sure why this is so.


In Cell A3:8, there are multiple "Ace Asset Development..." entries, these correspond to LH62, QJ68, HX82 in "Data" tab etc.


Similarly, I'd like cell B3:8 return the corresponding bidder position i.e. LH62 would be bidder number 8, QJ68 would be bidder number 11, HX82 would be bidder number 6 etc.


I'm not sure how to overcome the problem of multiple "Ace Asset Development.." entries though

 

[Hui]

Jasonleewkd


A few Big issues here:

Typically I would use a formula in cell Populate This!B3 like:

=MIN(IF(Data!P1:BBO134=A3,COLUMN(Data!P1:BBO134))) Ctrl+Shift+Enter

This will return the column No and then I'd do some maths to extract the Bidder No.


But this returns an error as the resultant array of 190146 values is larger than excel can handle


Second problem is that although you are looking up values in Column A, those values occur several times in different columns in the 'Populate This' sheet


I can't think of an easy way around this without going to VBA, but I suspect a UDF would slow the PC down to geological speed due to the size of the arrays


[Thinking out loud] Instead of having Bid 1 to Bid 32 be besides each other could they be placed below each other ? It would mean that Column A:K would have top be replicated for each bidder for each job ?