Vlookup Index

cwallace70

Board Regular
Joined
Mar 7, 2011
Messages
172
I have this formula =IF(COUNTIF(Sheet1!$A$2:$O$2,$A49),SUM(INDEX(Sheet1!$A$2:$O$59,0,MATCH(A49,Sheet1!$A$2:$O$59,0))))/2
that I am trying to convert to vlookup instead of Countif. I keep getting an error message when trying to convert to vlookup Is that possible to do with vlookup? and not sure what to replace the SUM or MATCH with

=IF(VLOOKUP(A3,Sheet1!$A$2:$O$2),SUM(INDEX(Sheet1!$A$2:$O$59,0,MATCH(A3,Sheet1!$A$2:$O$59,0))))
 

Attachments

  • lookup tab.png
    lookup tab.png
    17.5 KB · Views: 8
  • main tab.png
    main tab.png
    17.5 KB · Views: 8

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Why would you want to change it to vlookup? At a glance, that is not the correct function to use for what you are attempting to do, which is (part of) the reason why you are encountering errors.

On a side note, the first formula shouldn't be working either because the match range is not valid. Any result that it is giving you will be the result of luck more than accuracy.

There is not enough detail visible in your screen captures to identify exactly what you're trying to do, but it appears that you want to match A49 to row 2 of sheet 1, sum the column below the match if one is found, then divide the result by 2, is that correct? If so then the correct formula to use would be
Excel Formula:
=IFERROR(SUM(INDEX(Sheet1!$A$2:$O$59,0,MATCH(A49,Sheet1!$A$2:$O$2,0)))/2,0)
 
Upvote 0
Why would you want to change it to vlookup? At a glance, that is not the correct function to use for what you are attempting to do, which is (part of) the reason why you are encountering errors.

On a side note, the first formula shouldn't be working either because the match range is not valid. Any result that it is giving you will be the result of luck more than accuracy.

There is not enough detail visible in your screen captures to identify exactly what you're trying to do, but it appears that you want to match A49 to row 2 of sheet 1, sum the column below the match if one is found, then divide the result by 2, is that correct? If so then the correct formula to use would be
Excel Formula:
=IFERROR(SUM(INDEX(Sheet1!$A$2:$O$59,0,MATCH(A49,Sheet1!$A$2:$O$2,0)))/2,0)
My error. I do not want to sum. I want to lookup the text in column A and heading row 2 of the main tab and find the text on the lookup tab based on the heading in the same row and column A is the lookup value.
 
Upvote 0
Ok, so let me double check the details here. Please correct anywhere that I'm wrong.

A3 = "23311Direct Ordinary Life" in the Main Tab?
You want to find a match for that in A2:O2 of the Lookup Tab? (no match visible on the screen capture that I can see).
When a match is found, where do we go from there?
 
Upvote 0
Ok, so let me double check the details here. Please correct anywhere that I'm wrong.

A3 = "23311Direct Ordinary Life" in the Main Tab?
You want to find a match for that in A2:O2 of the Lookup Tab? (no match visible on the screen capture that I can see).
When a match is found, where do we go from there?
The match is also in column A of the lookup tab, but the 2nd criteria is the headings in row 2 of both tabs. on the main tab lookup A3 and the heading in B2 on the lookup tab and return the text or number in tht cell.
 
Upvote 0
The match is also in column A of the lookup tab, but the 2nd criteria is the headings in row 2 of both tabs. on the main tab lookup A3 and the heading in B2 on the lookup tab and return the text or number in tht cell.
I neglected to mention the results should be on main tab based on the heading and lookup data in col. A on main tab
 
Upvote 0
I think this could end up going round in circles trying to figure out from descriptions and the current screen captures.

Rather than referring to column in a generic manner, it would be better if you could stick to a single starting cell and whatever relates to it.

You were looking for A3 in your attempted formula, so we'll focus on that.

What is the whole range in the other sheet that should be searched for a match (Is $A$2:$O$2 the correct range for this)?
Which single cell in that range is the match for A3 found in?
Which cell should be returned as the result of the formula?

As you mention a second criteria, I will need the same 4 pieces of information for that as well. Where is the criteria, where is the range to search, which cell in the range to be searched contains the match to the criteria, and finally, which cell should be returned as the result?

It's getting late in my part of the world so this is probably my last reply for the day but I'll come back and have another look in the morning.
 
Upvote 0
I think this could end up going round in circles trying to figure out from descriptions and the current screen captures.

Rather than referring to column in a generic manner, it would be better if you could stick to a single starting cell and whatever relates to it.

You were looking for A3 in your attempted formula, so we'll focus on that.

What is the whole range in the other sheet that should be searched for a match (Is $A$2:$O$2 the correct range for this)? This is the heading's row on both tabs
Which single cell in that range is the match for A3 found in? the match is in column A of the lookup tab
Which cell should be returned as the result of the formula? if A3 is found in column A on the lookup tab and

As you mention a second criteria, I will need the same 4 pieces of information for that as well. Where is the criteria, where is the range to search, which cell in the range to be searched contains the match to the criteria, and finally, which cell should be returned as the result?

It's getting late in my part of the world so this is probably my last reply for the day but I'll come back and have another look in the morning
I have two tabs. The main tab contains an ID in column A. It also contains headings in row 2. all the other cells are blank.
The lookup tab contains the ID in column A and headings in row 2, it also contains the data that I want to transfer to the main tab. for example column B row 2 on the main tab heading is company, and the lookup tab has a heading in column M row 2.
how do I lookup the ID and the heading on the main tab based on the same criteria's on the lookup tab and return the information in the cell based on the ID and heading.
 
Upvote 0
I'm following what you want now, it would have been possible to figure out from the screen captures in post 1 if there were matching criteria on the 2 images.

You may need to change the ranges used so that they fit your sheet correctly.
Excel Formula:
=IFERROR(INDEX(Sheet1!$B$3:$O$59,MATCH($A3,Sheet1!$A$2:$A$59,0),MATCH(B$2,Sheet1!$A$2:$O$2,0)),"")
In the formula, Sheet1 refers to the Lookup tab.
Sheet1!$B$3:$O$59 refers to the data range on the lookup tab (excluding the ID column and Header row).
Sheet1!$A$3:$A$59 refers to the ID column on the lookup tab (start and end row must be the same as the data range above).
Sheet1!$A$2:$O$2 refers to the Header row on the lookup tab (start and end column must be the same as the data range above).
All of the above ranges should contain 4 $ symbols in the correct positions.

$A3 refers to the first ID on the Main tab. This must have a single $ symbol in front of the column letter only.
B$2 refers to the first Heading on the Main tab. This must have a single $ symbol between the column letter and row number.

If any of the $ symbols are entered incorrectly when you edit the formula then it will not work as expected.
 
Upvote 0
I'm following what you want now, it would have been possible to figure out from the screen captures in post 1 if there were matching criteria on the 2 images.

You may need to change the ranges used so that they fit your sheet correctly.
Excel Formula:
=IFERROR(INDEX(Sheet1!$B$3:$O$59,MATCH($A3,Sheet1!$A$2:$A$59,0),MATCH(B$2,Sheet1!$A$2:$O$2,0)),"")
In the formula, Sheet1 refers to the Lookup tab.
Sheet1!$B$3:$O$59 refers to the data range on the lookup tab (excluding the ID column and Header row).
Sheet1!$A$3:$A$59 refers to the ID column on the lookup tab (start and end row must be the same as the data range above).
Sheet1!$A$2:$O$2 refers to the Header row on the lookup tab (start and end column must be the same as the data range above).
All of the above ranges should contain 4 $ symbols in the correct positions.

$A3 refers to the first ID on the Main tab. This must have a single $ symbol in front of the column letter only.
B$2 refers to the first Heading on the Main tab. This must have a single $ symbol between the column letter and row number.

If any of the $ symbols are entered incorrectly when you edit the formula then it will not work as expected.
The results are 0 for everything when it should return the value or text in the cell
=IFERROR(INDEX(Sheet1!$B$3:$O$59,MATCH($A3,Sheet1!$A$3:$A$59,0),MATCH(S$2,Sheet1!$A$2:$O$2,0)),"")
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top