View Full Version : Lookup Help
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?
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)
: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.
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.
Please don't use this abbreviated form of text.
Apology for the same.:aw (i was wrong)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.