vlookup or match

=VLOOKUP($A2,$K$2:$O$51,COLUMN(),0)

to avoid the #NA# use

=if(iserr(VLOOKUP($A2,$K$2:$O$51,COLUMN(),0)),"",VLOOKUP($A2,$K$2:$O$51,COLUMN(),0))

What is column()?

You can use this formula in B2 copied across to E2 and then down the columns

=IF(ISNA(MATCH($A2,$K$2:$K$100,0)),"",INDEX(L$2:L$100,MATCH($A2,$K$2:$K$100,0)))

As you have no matches everything is balnk for your sample so  I added a matching code in A24

see attached,

regards, barry
27400796.xls

the vlookup formula needs to know which column to return the result from.


The column() function returns a number which tells which column to get it from

Something wrong with the formula. If the string is "10/24/2001"  date format, then return a wild number.

zhshqzyc,

If you have a lot of entries, the LookUp's can be slow. The attached on;y needs a single LookUp for each row. The cost is an extra column (column F) - a "helper column".

In column F...
=IF(COUNTIF($K$2:$K$51,A2)<>0,MATCH(A2,$K$2:$K$51,0),"")

Enter the following in column A (and then copy and paste from A to columns C, D and E)...
=IF($F2="","",INDEX(L$2:L$51,$F2,0))

Regards,
Brian. temp-V2.xls

zhshqzyc,

If the string is "10/24/2001"  date format, then return a wild number.

The easiest way is to format column E as required.

Regards,
Brian.

If using the VLOOKUP then rather than COLUMN better to use COLUMNS, e.g. in B2

=VLOOKUP($A2,$K$2:$O$51,COLUMNS($B2:B2)+1,0)

The advantage of this is that, unlike column, results will stay the same even if you insert a column to the left of the data.

Personally I prefer the INDEX/MATCH version, as Brian says you can use just one MATCH per row if you want.

regards, barry

Can somebody test this file?
temp.xls

After you have completed the formulas format column D as date

zhshqzyc,

Please see  attached.

Regards,
Brian. temp-V3.xls

If I applied the INDEX/MATCH formula I suggested above then that only gives matches for the top 3 - see attached, is that what you expect?

Note: I formatted the columns for date as appropriate

regards, barry
index-match.xls

Sorry, talking rubbish - there are more matches but formula range needs to be extended beyond my original 100 rows - I used this version in B2 copied across and down

=IF(ISNA(MATCH($A2,$K$2:$K$4000,0)),"",INDEX(L$2:L$4000,MATCH($A2,$K$2:$K$4000,0)))

see attached

regards, barry
INDEX-MATCH-2.xls

redmonndb,
Can you remove 0 for AWP3141 etc?
Also remove column F you designed and add column P if mtached.

barryhoudini - Eight matches per row for matching rows!?

>barryhoudini - Eight matches per row for matching rows!?

I don't understand, Brian....?

regards, barry

Barry,

For matching entries, each cell has two matches (one to check the entry exists, the second to get its row), 4 cells per row = 8 matches.

In situations like this, I've always felt that formulas could do with some kind of a variable.

Regards,
Brian.

Oh, I get you now, Brian

Yes, I agree. It's certainly more efficient to use the single MATCH per row......not everybody wants to use the additional column but its probably a good idea

regards, barry

Thanks, kindly, zhshqzyc!