CODEWITHSUNDEEP
excel
mmar
mmar

Reputation: 2020

how to get excel column index by searching with cell text?

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).

Upvotes: 5

Views: 67588

Answers (2)

Nelda.techspiress
Nelda.techspiress

Reputation: 643

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.

Upvotes: 0

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11151

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).

Upvotes: 12

Related Questions