please help - excel 1

[afbutlerandassoc]

here's my problem:

=INDEX(vendorrates,MATCH(A5,ratename),MATCH('Retail routing'!D3,[red]'LCR RATES'!D58:U58[/red],0)-1)

How can I look up the row and find the value if I don't specify which row to look for the value in (the red part)

This returns the right info, but it's not dynamic enough - I can't take the time to enter all the row array info for each lookup.

Thanks

 

[datadan]

afbutlerandassoc,

I don't exactly get what you are trying to do, but have you tried VLOOKUP? This will search an array for value instead of a specific row number.

 

[afbutlerandassoc]

Thanks Datadan,

Here's my problem - I have a large list (1036 rows) with some duplication of data (many instances of the same vendor name, however only one instance per row) and I need to find the row that corresponds to the destination, but I want to search for that row dynamically ( I can find the row (as in the example) and can find the result I am looking for when I enter the array exactly, but when it changes tomorrow I have to update over 1000 rows x 17 columns, and that is nigh unto impossible to do daily since each one is unique.

Thanks for any help you can give.

Ashok

 

[Hasit]

Have you considered naming the range you are searching?

Then use the name in your formula.....

 

[xlbo]

Try using INDEX and MATCH or possible ROW if they are in teh same sort order
Rgds
~Geoff~

 

[DaleWatson123321]

This was somewhat of a challenge. I tried various options, and finally came up with the following working formula.

=IF(I5="","",VLOOKUP(A5,vr,CHOOSE(FIND(I5,VLOOKUP(A5,vr,5,FALSE)&VLOOKUP(A5,vr,7,FALSE)&VLOOKUP(A5,vr,9,FALSE)&VLOOKUP(A5,vr,11,FALSE)&VLOOKUP(A5,vr,13,FALSE)&VLOOKUP(A5,vr,15,FALSE)&VLOOKUP(A5,vr,17,FALSE)&VLOOKUP(A5,vr,19,FALSE)&VLOOKUP(A5,vr,21,FALSE)),4,4,4,6,6,6,8,8,8,10,10,10,12,12,12,14,14,14,16,16,16,18,18,18,20,20,20),FALSE))

The formula looks up the text in column A, and concatenates the codes from the 9 different fields.

The FIND function references the code in the adjacent cell (I5) and is used to locate the position of this code within the concatenated text.

The CHOOSE arguments (positions) reference each of the fields containing the matching VALUE of each code.

Therefore, regardless of which field a code is located, the formula will return the value for that code.

I'm pleased that this solution resolved your rather unique task.

Best Regards, ...Dale Watson dwatson@bsi.gov.mb.ca