**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.*