vlookup or match

Hello,

Could you please look at the file attached?
For column A, the sample ID can be found in column K or may be not. If found, please fill column B,C,D,and E with the info in column L,M,N and O correspondly.
The attached is just a sample, I want a formula.
temp.xls
zhshqzycAsked:
Who is Participating?
 
redmondbConnect With a Mentor Commented:
zhshqzyc,

Please see attached. Please note, I've used Column Q rather than P as there's already data there. Obviously, a small change if you actually wanted to overwrite.

Regards,
Brian. temp-V4.xls
0
 
Saqib Husain, SyedEngineerCommented:
=VLOOKUP($A2,$K$2:$O$51,COLUMN(),0)
0
 
Saqib Husain, SyedEngineerCommented:
to avoid the #NA# use

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

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
zhshqzycAuthor Commented:
What is column()?
0
 
barry houdiniCommented:
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
0
 
Saqib Husain, SyedEngineerCommented:
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
0
 
zhshqzycAuthor Commented:
Something wrong with the formula. If the string is "10/24/2001"  date format, then return a wild number.
0
 
redmondbCommented:
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
0
 
redmondbCommented:
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.
0
 
barry houdiniCommented:
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
0
 
zhshqzycAuthor Commented:
Can somebody test this file?
temp.xls
0
 
Saqib Husain, SyedEngineerCommented:
After you have completed the formulas format column D as date
0
 
redmondbCommented:
zhshqzyc,

Please see  attached.

Regards,
Brian. temp-V3.xls
0
 
barry houdiniCommented:
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
0
 
barry houdiniCommented:
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
0
 
zhshqzycAuthor Commented:
redmonndb,
Can you remove 0 for AWP3141 etc?
Also remove column F you designed and add column P if mtached.
0
 
redmondbCommented:
barryhoudini - Eight matches per row for matching rows!?
0
 
barry houdiniCommented:
>barryhoudini - Eight matches per row for matching rows!?

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

regards, barry
0
 
redmondbCommented:
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.
0
 
barry houdiniCommented:
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
0
 
redmondbCommented:
Thanks, kindly, zhshqzyc!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.