[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

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
0
zhshqzyc
Asked:
zhshqzyc
  • 7
  • 6
  • 4
  • +1
1 Solution
 
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
 
zhshqzycAuthor Commented:
What is column()?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
redmondbCommented:
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
 
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 7
  • 6
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now