need help with a VLOOKUP formula

[AmrAndy93]

i need help with some formulas for this sheet i need to create

basically what i want the sheet to do is to locate the names in column 1 who have value greater than x in column 2 and then reflect it into a new tablet

now i'm very open minded like if there will have to be several sheets or if this can be done in one sheet

i am aware that this is a bit tricky as i have tried to use VLOOKUP formulas and they work but show one name or they show nothing when i try to get to look for like ">2000" which comes up with N/A

Example:

find the names of everyone who is paid more than 2,000 and put it in a new table

i basically need to put this formula in a new table and it reflects the info from the raw data

OK so here is a made up sample like table

[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Salary [/TD]
[/TR]
[TR]
[TD]23115[/TD]
[TD]John[/TD]
[TD]2.100[/TD]
[/TR]
[TR]
[TD]45326[/TD]
[TD]Jackie[/TD]
[TD]3.500[/TD]
[/TR]
[TR]
[TD]47839[/TD]
[TD]Katy[/TD]
[TD]1,900[/TD]
[/TR]
[TR]
[TD]51946[/TD]
[TD]Chris[/TD]
[TD]1,500[/TD]
[/TR]
</tbody>[/TABLE]








so to recap i need a formula to locate name with salary above 2,000 and put it in a different table like the below outcome

[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]Highest Paid Employees[/TD]
[/TR]
[TR]
[TD]John [/TD]
[/TR]
[TR]
[TD]Jackie [/TD]
[/TR]
</tbody>[/TABLE]

 

[NBVC]

Assuming your table is in A1:C5, and you want to make list in say column G.

=IFERROR(INDEX($B$2:$B$5,SMALL(IF($C$2:$C$5>2000,ROW($C$2:$C$5)-ROW($C$5)+1),ROWS($G$2:$G2))),"")

confirmed with CTRL+SHIFT+ENTER keys together not just ENTER, then copy down.

 

[bruderbell]

Assuming your table is in A1:C5, and you want to make list in say column G.

=IFERROR(INDEX($B$2:$B$5,SMALL(IF($C$2:$C$5>2000,ROW($C$2:$C$5)-ROW($C$5)+1),ROWS($G$2:$G2))),"")

confirmed with CTRL+SHIFT+ENTER keys together not just ENTER, then copy down.

NBVC,
I'm always trying to learn from you MVPs. I tackled this and came up with a formula quite similar to yours:

=IFERROR(INDEX(Table1[Name],MATCH(SMALL(IF(Table1[Salary]>$E$16,Table1[Salary]),ROWS($C$16:C16)),Table1[Salary],0)),"")

Is it just plain more efficient to resolve the IF to the row, vs matching as I did?
Thanks

 

[AmrAndy93]

i just tried it with CTRL+SHIFT+ENTER but the cell just remained blank but shows the formula in the top FX bar but nothing in actual cell

what could the problem be? could it be because i am using an Old version of Excel? by old i mean the 2007 one

 

[Weazel]

I'm thinking it might be the decimal points in your values but I guess that also depends on what part of the world you're in.

From where I am none of the values you posted are above 2000

 

[AmrAndy93]

well i'm in Egypt but i don't think it would make a difference in a formula

i tried the formula on a small scale of 4 numbers which were 3400 1600 2500 2100 i'm talking about the one NBCV wrote but then the cell remained blank and then i tried the second formula by bruderbell and that one came up with error in the first part of it Table1[Name] and kept saying formula is wrong basically

i'm thinking it might be cause this is an old version of excel

i'm basically still learning which why i'm using this old version but if it is a version problem then i can just upgrade it

i just came here for help cause as i said i'm still learning so any help is very well appreciated

 

[NBVC]

What version of Excel? If you are in 2003 or earlier, the IFERROR() function won't work... but if you didn't get an error, then most likely you are in 2007 or later version.

Try:

=LOOKUP(REPT("Z",255),CHOOSE({1,2},"",INDEX($B$2:$B$5,SMALL(IF($C$2:$C$5>2000,ROW($C$2:$C$5)-ROW($C$5)+1),ROWS($G$2:$G2)))))

confirmed with CTRL+SHIFT+ENTER. Does that work?

 

[Weazel]

just a modification to NBVC's formula

=IFERROR(INDEX($B$2:$B$5,SMALL(IF($C$2:$C$5>2000,ROW($C$2:$C$5)-ROW($C$2)+1),ROWS($E$2:$E2))),"") Control Shift Enter

Sorry NBVC, I'm not trying to correct your formulas

 

[NBVC]

Thanks for catching that Weazel. That was probably the problem

 

[AmrAndy93]

yeah the modification to NBVC's formula worked but it only show one result which is the first cell and doesn't allow me to drag it down
and when i paste it again and use CTRL+SHIFT+ENTER it comes with the same result

and i am using 2007 excel so i guess it isn't the version

NBVC i will try the second one you mentioned in a couple of minutes, i just need to run out for a moment