Finding a value within the defined column address of a specific range

**mike ryan** · 07-05-2012, 04:19 AM

Good morning all!

test.xlsI've attached a small workbook which I hope might help better explain what I need to be able to do. In short I have the column address of one value (the name) and want to use this column address number to look up the value in coluimn before the equivalent column (where the address value is greater than 1) under the VALUES half of my data.

For example, on the spreadsheet where it says Mike, I need to come up with a formula that returns the value of 900 from cell A2.

Does that make sense to any of you?

**Marcol** · 07-05-2012, 04:54 AM

Does that make sense to any of you?

Not really, but try this array formula in J2

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter not just enter.
Drag/Fill Down.

**mike ryan** · 07-05-2012, 05:22 AM

Thanks Marcol,

That's almost it!

It's returning the value from the second column which is great, but I actually need it to return the value from the 1st column. Ie, where the column value in I is 3, it will return the value from the 2nd column in the values range.

**Ace_XL** · 07-05-2012, 05:34 AM

Change marcol's formula to

=INDEX(A:D,ROW(),MAX(IF(ISTEXT(E2:H2),COLUMN(A:D)-1)))

Confirm with Ctrl+Shift+Enter

**Marcol** · 07-05-2012, 05:54 AM

I don't understand what you are trying to do, or why you are doing it this way.
Try this array in J2

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter not just enter.
Drag/Fill Down.

In I2 you could have

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter not just enter.
Drag/Fill Down.
Then J2 might be

Please Login or Register  to view this content.

Drag/Fill Down.

Would this not suffice?

Please Login or Register  to view this content.

**mike ryan** · 07-05-2012, 06:03 AM

Thank you both, the -1 amendment worked perfectly. I do apologise for being a bit vague, I'm afraid the actual spreadsheet I'm working on is covering 6 different column ranges across an 80mb workbook of 143 columns and 20000 + rows and it would take a mini essay to properly explain the end result that I wanted.

I know I didn't go into a great deal of detail, but I guessed (correctly I might add!) that someone here would have a perfectly simple solution to what I was looking for.

Yet another of my headaches solved by this forum! Thanks again.