Lookup Question???

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
 
F

Frank Kabel

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
 
A

af_jester

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
 
F

Frank Kabel

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

af_jester

=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!
 
Top