VLOOKUP USING INDEX, MATCH, AND IF PLEASE HELP

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tomkat743 - 31 Mar 2006 21:16 GMT

=INDEX(Sheet1!$A$1:$C$44,MATCH(I2,Sheet1!$A$1:$A$44,0),2)
=INDEX(Sheet1!$A$1:$C$44,MATCH(I2,Sheet1!$A$1:$A$44,0),3)

How can I dynamically choose which column ie;2 or 3 this formula returns
data from based on the assigned installer in CELL C2?

The possible installers in column C would range from 9501 to 9599 some
installers are employees and would need to pull from column 2 and other
installers are contractors and would need to pull from column 3.

Reply to this Message

L. Howard Kittle - 31 Mar 2006 21:53 GMT

If the numbers are consecutive for employees and contractors, say 9510 to
9550 for employees and the others are contractors, maybe something like:

=IF(C2<9551,INDEX(Sheet1!$A$1:$C$44,MATCH(I2,Sheet1!$A$1:$A$44,0),2),
INDEX(Sheet1!$A$1:$C$44,MATCH(I2,Sheet1!$A$1:$A$44,0),3)

If there is a mixture, I don't know how you would handle that.

HTH
Regards,
Howard

> =INDEX(Sheet1!$A$1:$C$44,MATCH(I2,Sheet1!$A$1:$A$44,0),2)
> =INDEX(Sheet1!$A$1:$C$44,MATCH(I2,Sheet1!$A$1:$A$44,0),3)
[quoted text clipped - 5 lines]
> installers are employees and would need to pull from column 2 and other
> installers are contractors and would need to pull from column 3.

Reply to this Message

Tomkat743 - 31 Mar 2006 22:31 GMT

Thank you, what if I just want it to equal whatever c2 is at the time? can I
create a table for contractor tech numbers and another one for employee tech
numbers. Or just one table for employee tech numbers and if it doesn't find
it there revert to the second string. So if value was false it would go to
string 2
=IF(C2=A VALUE IN AN EMPLOYEE
TABLE,TRUE=INDEX(SHEET1!$A$1:$C$44,MATCH(I2,Sheet1!$A$1:$A$44,0),2),=INDEX(Sheet1!$A$1:$C$44,MATCH(I2,Sheet1!$A$1:$A$44,0),3)

> If the numbers are consecutive for employees and contractors, say 9510 to
> 9550 for employees and the others are contractors, maybe something like:
[quoted text clipped - 17 lines]
> > installers are employees and would need to pull from column 2 and other
> > installers are contractors and would need to pull from column 3.

Reply to this Message

L. Howard Kittle - 31 Mar 2006 22:47 GMT

Hmmm, I might be missing something here. The formula I posted says if TRUE
do column 2, if FALSE do column 3.

The C2 values have to have some method of cooperation, like every number
below a certain value is an employee. So if the formula returns False it
does column 2.

Regards,
Howard

Reply to this Message

L. Howard Kittle - 01 Apr 2006 00:39 GMT

Should say if returns false does column 3.

H

> Hmmm, I might be missing something here. The formula I posted says if
> TRUE do column 2, if FALSE do column 3.
[quoted text clipped - 15 lines]
>> installers are employees and would need to pull from column 2 and other
>> installers are contractors and would need to pull from column 3.

Reply to this Message