PDA

View Full Version : Lookup Help



xls
08-14-2010, 04:43 AM
I have data in table size of x into y, There is data in each cell.

I wants to lookup particular value i dont know in which column or raw it is and i want to return value to the left of it & right to it in the different cell.

Pl help me, thanks in advance.

mikerickson
08-14-2010, 10:17 AM
Could you attach a workbook with a redacted example of your data, your inputs and the results you want?

xls
08-15-2010, 09:51 PM
I have attached test Data.

Thanks for Replying.

mikerickson
08-16-2010, 06:14 AM
I used these Names:

Name:dataRange RefersTo: =Sheet1!$B$3:$G$7

Name:columnOfInterest RefersTo: =INDEX(dataRange,,1-COLUMN(dataRange)+MIN(IF(dataRange=Sheet1!$B$12,COLUMN(dataRange))))

Name: colOfInterestLeft RefersTo: =INDEX(dataRange,,-COLUMN(dataRange)+MIN(IF(dataRange=Sheet1!$B$12,COLUMN(dataRange))))

Name: colOfInterestRight RefersTo: =INDEX(dataRange,,2-COLUMN(dataRange)+MIN(IF(dataRange=Sheet1!$B$12,COLUMN(dataRange))))

These CSE formulas should do what you want

=INDEX(colOfInterestLeft, MATCH($B$12,columnOfInterest,0), 1)
=INDEX(colOfInterestRight, MATCH($B$12,columnOfInterest,0), 1)

These need to be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

xls
08-16-2010, 09:51 AM
:bow: GR8 work.

Is is possible so solve this using UDF

mikerickson
08-17-2010, 06:02 AM
One could translate that code to a UDF. I would do a straight forward transliteration into VBA, as the .Find method does not work in a UDF that is called from the worksheet.

xls
04-04-2011, 11:58 AM
can i have macro for dis, bcos array is not workin wen i have to use dis for multiple data

mdmackillop
04-04-2011, 01:20 PM
can i have macro for dis, bcos array is not workin wen i have to use dis for multiple data
Please don't use this abbreviated form of text.

xls
04-04-2011, 11:14 PM
Please don't use this abbreviated form of text.

Apology for the same.:aw (i was wrong)