Lookup Question???

  • Thread starter Thread starter af_jester
  • Start date Start date
A

af_jester

is there any function that can search through a list of values in on
collumn and return a corresponding value within the row. (i.e if C2
is the value I am searching for, it will return the value for F24)
Also it would need to do it for any and all matching values. Any hel
is appreciated...

Chri
 
Hi
for the first instance of an occurence you can use VLOOKUP
e.g.
=VLOOKUP("lookup_value",C1:F100,4,0)

If you want to return multiple values you may use the following array
formula (entered with cTRL+SHIFT+ENTER)
=INDEX($F$1:$F$100,SMALL(IF($C$1:$C$100="lookup_value",ROW($C$1:$C$100)
),ROW(1:1)))
and copy this down as far as needed
 
The problem i am encountering (i think the Vvlookup might work) is tha
then column that I want to search in is the last column where as th
result is in the first column. Vlookup has it set to where it looks u
the first column in the array.... I think I am missing something...
but I cant figure out what it is.


Chri
 
Hi
then try something like
=INDEX(F1:F100,MATCH("lookup_value",C1:F100,0))

the array formula will still work. change it to
=INDEX($C$1:$C$100,SMALL(IF($F$1:$F$100="lookup_value",ROW($F$1:$F$100)
),ROW(1:1)))
 
=INDEX('LEP - 104'!B2:E252,MATCH("DUE",'LEP - 104'!E2:E252,0),1)


that forumla worked for what I needed however, my next issue comes whe
I try to use that formula to look for another result in the same arra
with the same factors. What will I have to do to exclude the resul
already found?


here is an example.....

i have a list of inspections that when they come due, I want to have i
read down the list and when any(and all) inspections are due, I want i
to list the piece (or pieces) of equipment.

Thanks again for the help!
 
Back
Top