find row, find column and return the value there 1

[afbutlerandassoc]

Hi, I have a excel question: I have a column of names, and I can use a match (=MATCH(A5,ratename))(= row 58) to find the row number, but I need to find a value in that row and then return the value to the left of it - I can find the column number if I specify the row array (=MATCH('Retail routing'!D3,[red]'LCR RATES'!A58:U58[/red],0)-1))(= column 4) but I can't get the two to work together so that I can deliver the value in row 58, column 4) Please help I'm about to lose some hair

 

[xlbo]

try using either a combination of INDEX and match or LOOKUP. LOOKUP will allow you to search left from a found item which seems to be what you need
HTH
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