Lookup w/ aliases

**twodwarfminimum** · 10-12-2009, 05:39 PM

Not sure if my Title made any sense, but I'm not very familiar with the lingo. Honestly my problem is I have no training, just self taught. Anyways, I have a lookup table that I need to expand and not sure if it is even possible?

With each name, there is a property number. But there are variances on the names. So is there a way to tell it to look in column b,c & d for a match rather than just look in b like it does now? Again I apologise if I sound like an idiot, I've attached a sample to explain more.

I paste the names on sheet1 and it pulls from sheet2

Thank you so much for any help that you can offer on this!

**teylyn** · 10-12-2009, 05:52 PM

Ugly, but one possibility:

=INDEX(Sheet2!$A$2:$A$4,IF(ISNA(MATCH(Sheet1!$B3,Sheet2!$B$2:$B$4,0)),IF(ISNA(MATCH(Sheet1!$B3,Sheet2!$C$2:$C$4,0)),MATCH(Sheet1!$B3,Sheet2!$D$2:$D$4,0),MATCH(Sheet1!$B3,Sheet2!$C$2:$C$4,0)),MATCH(Sheet1!$B3,Sheet2!$B$2:$B$4,0)))

**twodwarfminimum** · 10-12-2009, 06:57 PM

Well I'm not real good at reading French, but it works?..lol Thanks so much.

I do have a favor, if it's not too much trouble though. I don't wan't to be one of those people that just sponge off those that know, I'ld like to learn. Is there any way you could break it down for me as to what is doing what?

Again, thanks so much!!

**teylyn** · 10-12-2009, 07:12 PM

Hi,

basically, I'm taking your INDEX/MATCH formula, but before applying the MATCH, the formula check sif the MATCH returns an error. If so, it goes on to try and find a match in the next column, and so on.

break it down into bits like this

=INDEX(Sheet2!$A$2:$A$4,
    ' now the MATCH bit
    IF(
         ISNA(MATCH(Sheet1!$B3,Sheet2!$B$2:$B$4,0)),  ' if match is an error in column B then
               'look at column C
               IF(
                     ISNA(MATCH(Sheet1!$B3,Sheet2!$C$2:$C$4,0)), ' if match is an error in column C then
                                'look at column D and return this for MATCH
                                MATCH(Sheet1!$B3,Sheet2!$D$2:$D$4,0),
               'else = if a match is found in column C, return this for MATCH
                                MATCH(Sheet1!$B3,Sheet2!$C$2:$C$4,0)),
               ' end of inner IF
         'else = if a match is found in column B, return this for MATCH
         MATCH(Sheet1!$B3,Sheet2!$B$2:$B$4,0)))
     'end of outer IF