I could use some similar help with this as I have tried but there are some things not working still. it might because of my A2
I have a spreadsheet made up for trips listing companies, codes, hotels, rates, etc.
What I am trying to do is select the code and then have the spreadsheet populate all hotels for that coded company.
I am using in A2 a data validation listing of all codes, so I can drop down the list and select the code. The greatest number of hotels for a company is 7.
My Example
A B C D
001 NWP EXTENDED STAY $140.00
001 NWP HOLIDAY INN $140.00
001 NWP LAQUINTA $129.00
017 AC HAMPTON INN $109.00
017 AC HOLIDAY INN $89.10
017 AC COUNTRY INN $123.49
019 AFS BW+ $120.00
019 AFS FAIRFIELD INN $104.00
030 BBB COURTYARD $100.00
047 TAZ COMFORT STES $101.00
047 TAZ QUALITY INN $84.15
047 TAZ HOLIDAY INN $80.00
What I want to return in my results is after I select from my A1 = Data Validation list of say, 017, it would look like this
A B C D
CODE CO HOTEL RATES
A2=017 AC HAMPTON INN $109.00
2 HOLIDAY INN $89.10
3 COUNTRY INN $123.49
4 #NA #NA
5 #NA #NA
6 #NA #NA
7 #NA #NA
I have used vlookup for rows 1-7, but it is always grabbing the next code/company, so there is a match problem; i have also used index and match, but the same is happening.
I think I am on the right track, but again, in the example above, rows 4-7 are still bringing in data instead of #NA.
Thoughts?
using
=VLOOKUP($A2,DATA!$A$2:$K$320,4, FALSE) = where DATA! is the next sheet with all of the er data
=INDEX(DATA!A2:$K$320,MATCH(A2,DATA!A2:A320,0)+1,4)
=INDEX(DATA!A2:$K$320,MATCH(A2,DATA!A2:A320,0)+6,4)
THANKS FOR THE LOOK SEE!
Bookmarks