5

I have a excel file which has header row and many columns. In row 1, column 10 i have cell value as 'Request'. i need to get this cell index by searching with the value "Request". Is there any way without iterating through columns (without using for loop).

Noel
  • 10,152
  • 30
  • 45
  • 67
mmar
  • 1,840
  • 6
  • 28
  • 41
  • Are there more than one cell with 'Request'? If there are more than one cell with 'Request', do you need the index of all the cells with 'Request'? – Jerry Aug 28 '13 at 05:03
  • You'll need to be a little clearer as to what you want, and how you want to get it. Are you using VBA or excel functions? If you post a sample, that would help a lot. – Monty Wild Aug 28 '13 at 05:04
  • I am using Soapui+Groovy to automate my test case with the help of Apache POI for getting data from excel. I need to write my request and response after execution in the same excel on the 'Request' and 'Response' columns. So i need to get the column index which has the name 'Request' in the excel file (it will always be Row '0', as it is header row). So that if i get the index number i can use that for writing my request in the corresponding column index on required nth row. I know we can access thro' for loop. but i am trying to find another way without using for loop. Please let me know. – mmar Aug 28 '13 at 16:03

2 Answers2

12

Sample: You are looking in range C2:J2 and H2 has "Request":

 =MATCH("Request"; C2:J2; 0)

will return 6 (column H is the 6th column counting from column C).

LS_ᴅᴇᴠ
  • 10,823
  • 1
  • 23
  • 46
  • I am using Soapui+Groovy to automate my test case with the help of Apache POI for getting data from excel. I need to write my request and response after execution in the same excel on the 'Request' and 'Response' columns. So i need to get the column index which has the name 'Request' in the excel file (it will always be Row '0', as it is header row). So that if i get the index number i can use that for writing my request in the corresponding column index on required nth row. I know we can access thro' for loop. but i am trying to find another way without using for loop. Please let me know – mmar Aug 28 '13 at 17:20
  • I don't know Apache POI, but Excel as built-in function `MATCH` which works in cells formulas and also in scripting. However I find myself preferable using programmatic methods instead of `Excel.WorksheetFunction` methods. – LS_ᴅᴇᴠ Aug 29 '13 at 07:30
0

The original question did not state using Apache POI to search the values of the title row. In this case you will need to use the POI methods getCell() and getCellValue() on row 0 and iterate through those until you find the value you're looking for, then use POI to insert the row - How to insert a row between two rows in an existing excel with HSSF (Apache POI). For examples using the getCell() and getCellValue() see this SO post;

The Match formula listed will not work in your case since the formula doesn't get evaluated until the spreadsheet is rendered. But it's a great formula to use in Excel.

Nelda.techspiress
  • 643
  • 12
  • 32