Question description:
This user has given permission to use the problem statement for this
blog.
V lookup formula to populate fields based on a criteria
Solved by V. U. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
17/04/2018 - 12:53
Hi, It's pleasure to help you today.
Excelchat Expert
17/04/2018 - 12:53
Please share more details of the requirement.
Excelchat Expert
17/04/2018 - 12:54
Vlookup read one information at a time.
User
17/04/2018 - 12:55
hi there
User
17/04/2018 - 12:55
th me
User
17/04/2018 - 12:55
i pressed save on my doc
User
17/04/2018 - 12:55
ing funny
Excelchat Expert
17/04/2018 - 12:55
Hi
Excelchat Expert
17/04/2018 - 12:55
Please describe the problem
Excelchat Expert
17/04/2018 - 12:55
Add info in the preview.
Excelchat Expert
17/04/2018 - 12:56
And we can discuss the requirements.
User
17/04/2018 - 12:56
so i have a column called registrations
User
17/04/2018 - 12:56
and i have another column called cost per regisration
Excelchat Expert
17/04/2018 - 12:56
I have provided an example in the preview.
User
17/04/2018 - 12:57
the cost per registration needs to refer to a separate tab
User
17/04/2018 - 12:57
that highlights costs per country and per application type
Excelchat Expert
17/04/2018 - 12:58
Please share sample info in the preview. I can bring the cost into different sheet.
User
17/04/2018 - 12:58
I want to generate a vlook up formula to populate thr cost per registration by picking up the data in the other tab
Excelchat Expert
17/04/2018 - 12:58
Please check the preview. The result is in different tab.
User
17/04/2018 - 12:59
yes but the data is larger
Excelchat Expert
17/04/2018 - 01:00
You can apply the same formula.
Excelchat Expert
17/04/2018 - 01:01
You can simply the formula to any number of rows.
User
17/04/2018 - 01:02
yes but its more than A:b
User
17/04/2018 - 01:02
there is soo much
User
17/04/2018 - 01:02
let me paste my data
Excelchat Expert
17/04/2018 - 01:03
You an add them. Please paste the data. I can review it. But, at the end, only one column is input and another is output.
User
17/04/2018 - 01:04
done
Excelchat Expert
17/04/2018 - 01:05
Ok. What is the requirement and what columns in need to refer?
User
17/04/2018 - 01:05
so i want the data to be populated on column o
User
17/04/2018 - 01:05
of planned sheet
User
17/04/2018 - 01:05
number of registrations is on column N of same sheet
User
17/04/2018 - 01:06
cost per registration is based on average fee tab
User
17/04/2018 - 01:06
where country needs to match in both tab
Excelchat Expert
17/04/2018 - 01:06
Ok.
Excelchat Expert
17/04/2018 - 01:06
County is column J?
Excelchat Expert
17/04/2018 - 01:06
I am working on it.
User
17/04/2018 - 01:07
and column H of planned tab (Secondary Type
)
User
17/04/2018 - 01:07
needs to match the column titles in the average fee tab
User
17/04/2018 - 01:07
please and thanks
Excelchat Expert
17/04/2018 - 01:07
I use offset and match functions.
Excelchat Expert
17/04/2018 - 01:07
They are much suitable in this case.
User
17/04/2018 - 01:07
ok
User
17/04/2018 - 01:07
not v lookup
User
17/04/2018 - 01:07
ok
Excelchat Expert
17/04/2018 - 01:08
yes. Vlookup is not preferred when working with 2 columns.
Excelchat Expert
17/04/2018 - 01:08
I will get back to you in few minutes.
User
17/04/2018 - 01:09
ok thanks
User
17/04/2018 - 01:09
t you understand the criteria yes?
Excelchat Expert
17/04/2018 - 01:09
Yes. I am working on.
Excelchat Expert
17/04/2018 - 01:11
=offset('Average fee'!$A$1,match(J2,'Average fee'!$A:$A,0)-1,match(H2,'Average fee'!$1:$1,0)-1,1,1)
Excelchat Expert
17/04/2018 - 01:11
Please find the formula
Excelchat Expert
17/04/2018 - 01:12
.
[Uploaded an Excel file]
Excelchat Expert
17/04/2018 - 01:12
Please review it. The formula can be copied downwards.
Excelchat Expert
17/04/2018 - 01:13
Please make sure the text are matching in the average fees table and Planned table.
Excelchat Expert
17/04/2018 - 01:13
If there is an exact match, it works fine.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
