Excel Lookup
I need to be able to look in an array that contains both text and numbers for
a text a value in a row. When that value is found I need the amount from the 100 row in the column where the text value I was searching for was found. I have tried using hlookup, lookup, find but have not had any success. Can anyone be of assistance. Thanks, |
Excel Lookup
Need a more detailed explanation.
Biff "Brian" wrote in message ... I need to be able to look in an array that contains both text and numbers for a text a value in a row. When that value is found I need the amount from the 100 row in the column where the text value I was searching for was found. I have tried using hlookup, lookup, find but have not had any success. Can anyone be of assistance. Thanks, |
Excel Lookup
Biff,
Here a quick expample A B C D 1 Name Rt 1 Rt 2 Rt 3 3 Net Sales 100.00 200.00 300.00 5 Name Rt 4 Rt 5 Rt 6 7 Net Sales 400.00 500.00 600.00 I would want the array to search A1:D7 find the cell with the text Rt 5 then supply the amount that is two rows below it. in this case 500.00. Does this example help? Thanks, Brian "Biff" wrote: Need a more detailed explanation. Biff "Brian" wrote in message ... I need to be able to look in an array that contains both text and numbers for a text a value in a row. When that value is found I need the amount from the 100 row in the column where the text value I was searching for was found. I have tried using hlookup, lookup, find but have not had any success. Can anyone be of assistance. Thanks, |
Excel Lookup
Ok.......
Try this: Array entered using the key combination of CTRL,SHIFT,ENTER: H1 = lookup value = RT 5 =OFFSET(A1,MAX((A1:D7=H1)*(ROW(A1:D7)))+1,MAX((A1: D7=H1)*(COLUMN(A1:D7)))-1) Biff "Brian" wrote in message ... Biff, Here a quick expample A B C D 1 Name Rt 1 Rt 2 Rt 3 3 Net Sales 100.00 200.00 300.00 5 Name Rt 4 Rt 5 Rt 6 7 Net Sales 400.00 500.00 600.00 I would want the array to search A1:D7 find the cell with the text Rt 5 then supply the amount that is two rows below it. in this case 500.00. Does this example help? Thanks, Brian "Biff" wrote: Need a more detailed explanation. Biff "Brian" wrote in message ... I need to be able to look in an array that contains both text and numbers for a text a value in a row. When that value is found I need the amount from the 100 row in the column where the text value I was searching for was found. I have tried using hlookup, lookup, find but have not had any success. Can anyone be of assistance. Thanks, |
All times are GMT +1. The time now is 01:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com