1

Am having some issue using the VLOOKUP function in excel. Am a little new to excel and puzzled as to how to solve this problem. I have two excel sheets.

Sheet 1 looks like:

Col1   Col2    Col3
HIC    GSN     ND11
H1C    00214   0212107
C4I    07287   0214380
L1A    07731   0214501

Sheet 2 looks like:

Col1         Col2   Col3    Col4    Col5     Col6    Col7
Condition    HIC1   HIC1    HIC1    GSN      GSN     GSN
AMA          B60     B61    B62     02934   02935
ALD          H1A     H1C            04821   03473
HEC          W0A     W0B

The VLOOKUP formula that am using is:

=VLOOKUP(B2,'Conditions.xlsx]Conditions'!$B$2:$BH$24,60,0)

Here B2 refers to Sheet 1, Column 1. Index Number 60 is a repeated Column 1 in Sheet 2. Is there a better way to lookup the COl1 and Col2 in Sheet one to extract the Conditions Columnin Sheet 2?

Any help would be great.

Sage Mohan
  • 155
  • 1
  • 1
  • 8
  • What's the error? – Andrew L Feb 15 '17 at 20:12
  • @AndrewL. Its shows #N/A – Sage Mohan Feb 15 '17 at 20:31
  • Is it intentional that you are looking for the value in the 60th column from B1? I would try an example using only column A and B to make sure it works. You probably don't need the file name in there- I'm assuming that's a typo? – Andrew L Feb 15 '17 at 20:40
  • Conditions sheet is a separate worksheet (table 2) that am checking the column 1 and column 2 of Table 1 against. – Sage Mohan Feb 15 '17 at 20:42
  • Worksheet or workbook? The form for selecting another worksheet in the same Workbook is- =Sheet2!A1 – Andrew L Feb 15 '17 at 22:10
  • @AndrewL. I see what yo mean, that is a typo. It is a worksheet. In this case worksheet 2 (Sheet 2). – Sage Mohan Feb 16 '17 at 15:21
  • At this point I would agree with the poster below that this would be better suited for an Index, Match. Highlighting B through BH is probably not efficient and is open to all sorts of error. Either way you need to scale it down and get it to work with a small table before expanding to this kind of size. – Andrew L Feb 16 '17 at 15:25
  • @AndrewL, I ended up using an Index Match Function but restructured the table in Sheet 2 to make it easier to Index Match. Thank you. – Sage Mohan Feb 17 '17 at 14:10

1 Answers1

1

Read this: How to use INDEX MATCH instead of VLOOKUP

But what happens if you want to look from right to left? VLOOKUP simply can't do that. INDEX/MATCH can.

UndeadBob
  • 1,110
  • 1
  • 15
  • 34
  • I read up on Using Index and Match in Excel. What am trying to find is if instead of giving the match function one column at a time to index, for eg. INDEX(Classes!$A$2:$A$38,MATCH(B3,Classes!$B$2:$B$38,0)) , If I can enter an entire table in the match function, like INDEX(Classes!$A$2:$A$38,MATCH(B3,Classes!$B$2:$AT$38,0)). Hope this makes some sense. – Sage Mohan Feb 15 '17 at 20:38